Shulou(Shulou.com)06/01 Report--

One: experimental background

The experiment of homework exercises when Ma GE Education learned the chapter of MYSQL.

Second: experimental preparation

1. First check whether mariadb exists on the virtual machine: rpm-qa mariadb*, if so, uninstall it

2. Find the official website of mariadb through yum info mariadb, and download the latest version of mariadb, mariadb-10.2.9-linux-x86_64.tar.gz.

Three: experimental steps

1. Create a mysql user

[root@localhost ~] # getent passwd mysql # check whether the user exists, and if not, execute the following code to create [root@localhost ~] # useradd-d / app/mysqldb-r-m-s / sbin/nologin mysql # to create a mysql account and specify the home directory (which can be customized), and set shell to nologin for security reasons

2. Copy the latest installation package to the linux virtual machine and extract it.

[root@localhost ~] # rz # use rz to copy the downloaded installation package under windows into Linux [root @ localhost ~] # tar xvf mariadb-10.2.8-linux-x86_64.tar.gz-C / usr/local/ # extract (because it is a compiled binary file, the directory has been made and must be placed in / usr/local So add-C to extract to the specified directory) [root@localhost ~] # cd / usr/local/ # go to this directory [root@localhost / usr/local] # ln-s mariadb-10.2.8-linux-x86_64/ mysql # to create a soft link (because the name must be mysql)

3. Create a configuration file for mysql

[root@localhost / usr/local] # cd / usr/local/mysql/ # go to the newly created soft link directory [root@localhost / usr/local/mysql] # mkdir / etc/mysql/ # because the default configuration file at compile time is under / etc/mysql, so create a mysql directory [root@localhost / usr/local/mysql] # ls support-files/ # configuration file has been prepared Put it in / usr/local/mysql/support-files directory binary-configure mysqld_multi.servermagic mysql-log-rotatemy-huge.cnf mysql.servermy-innodb-heavy-4G.cnf policymy-large.cnf wsrep.cnfmy-medium.cnf wsrep_notifymy-small.cnf [root@localhost / usr/local/mysql] # cp support-files/my-huge.cnf / etc/mysql/my.cnf # there are several Select one of them to copy to a specific directory / etc/mysql/ and rename it to a specific configuration file name my.cnf [root@localhost / usr/local/mysql] # vim / etc/mysql/my.cnf # Edit the configuration file [mysqld] # add the following three lines under [mysqld]. The first line must be added, and the last two lines are for easy management and performance optimization. Datadir = / app/mysqldb # indicates the database storage path innodb_file_per_table = on # uses the innodb engine, and makes each table a separate file, which makes it convenient to copy a single table later. Skip_name_resolve = on # ignores reverse parsing in order to speed up access

4. Create a system database

# if the database file has been prepared, put it in / usr/local/mysql/scripts/mysql_install_db [root@localhost / usr/local/mysql] # scripts/mysql_install_db-- user=mysql-- datadir=/app/mysqldb # when executing the previous command, it can only be executed under / usr/local/mysql, not under scripts. [root@localhost / usr/local/mysql] # ls / app/mysqldb/ # will report an error to verify that the database generates aria_log.00000001 ib_logfile0 multi-master.info mysql-bin.000003aria_log_control ib_logfile1 mysql mysql-bin.000004ib_buffer_pool ibtmp1 mysql-bin.000001 mysql-bin. Indexibdata1 localhost.localdomain.pid mysql-bin.000002 performance_ schema [root @ localhost / usr/local/mysql] #

5. Try to start the mysql service

[root@localhost / usr/local/mysql] # cp support-files/mysql.server / etc/init.d/mysqld # put the service script in a specific directory [root@localhost / usr/local/mysql] # chkconfig-- list mysqld # check whether the mysqld service is generated If it is not generated, use the following command to add [root@localhost / usr/local/mysql] # chkconfig-- add mysqld [root@localhost / usr/local/mysql] # service mysqld start # I reported this error because, in order to do an experimental demonstration, I deliberately deleted and rebuilt the original mysql user, resulting in no subordinate group Restarting mysqld (via systemctl): Job for mysqld.service failed because the control process exited with error code. See "systemctl status mysqld.service" and "journalctl-xe" for details. [FAILED] [root@localhost / usr/local] # chown-R mysql:mysql / var/log/mariadb/ # change the master group of / var/log/mariadb/ and its child files to mysql

6. Create a log file

My original / var/log/mariadb/ directory file already exists (the problem in 5), so there is no need to create it. Without this log file, when starting the service in step 5, it will make an error saying that the log file / var/log/mariadb was not found. We just need to create it.

[root@localhost / usr/local/mysql] # mkdir / var/log/mariadb/ # first create the log file directory [root@localhost / usr/local/mysql] # chown mysql / var/log/mariadb/ # this step does not have the file / var/log/mariadb/ before, so you need to change its owner to mysql after creation. In this way, mysql will have permission to create or delete subfiles under it [root@localhost / usr/local/mysql] # service mysqld start # and restart the service Starting mysqld (via systemctl): [OK] [root@localhost / usr/local/mysql] #

7. Add PATH variable

# because mysql is placed under / usr/local/mysql/bin, its path can be written to the configuration file, so there is no need to write the absolute path. [root@localhost / usr/local/mysql] # vim / etc/profile.d/mysql.sh PATH=/usr/local/mysql/bin:$PATH [root@localhost / usr/local/mysql] #. / etc/profile.d/mysql.sh # make it effective [root@localhost / usr/local/mysql] # echo $PATH / usr/local/mysql/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin [root@localhost / usr/local/mysql] #

8. Initialize, run the security script and set the password

[root@localhost / usr/local/mysql] # mysql_secure_installation # run the security script NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY!In order to log into MariaDB to secure it, we'll need the currentpassword for the root user. If you've just installed MariaDB, andyou haven't set the root password yet, the password will be blank,so you should just press enter here.Enter current password for root (enter for none): # prompt for password here, because we haven't set it before, so Enter directly enter OK, successfully used password, moving on...Setting the root password ensures that nobody can log into the MariaDBroot user without the proper authorisation.Set root password? [YPop] y # asks whether to set a password for root, and running a security script is for security, so of course we have to set a password! Enter "y" and enter New password: # enter password Re-enter new password: # re-enter (to enter both sides to confirm) Password updated fully loaded Reloading privilege tables.. ... Success!By default, a MariaDB installation has an anonymous user, allowing anyoneto log into MariaDB without having to have a user account created forthem. This is intended only for testing, and to make the installationgo a bit smoother. You should remove them before moving into aproduction environment.Remove anonymous users? [YPop] y # whether to remove anonymous users, for security, we enter "y" to remove anonymous users and prevent them from logging in. Successful normally, root should only be allowed to connect from 'localhost'. Thisensures that someone cannot guess at the root password from the network.Disallow root login remotely? [YBO] n # whether remote login is disabled or not, so enter "n" and enter. Skipping.By default, MariaDB comes with a database named 'test' that anyone canaccess. This is also intended only for testing, and should be removedbefore moving into a production environment.Remove test database and access to it? [YPop] y # whether to remove test database files, "y | n" is fine. I chose y-Dropping test database... here. ... Success!-Removing privileges on test database... ... Success!Reloading the privilege tables will ensure that all changes made so farwill take effect immediately.Reload privilege tables now? [YBO] y # whether to reload, that is, whether to make the above configuration effective immediately, of course, so "y". Success!Cleaning up...All done! If you've completed all of the above steps, your MariaDBinstallation should now be secure.Thanks for using MariaDB!

Four: experimental test

[root@localhost / usr/local/mysql] # mysql-uroot-p # because we have set the password, we need to add the password to log in. You can either "- p password" or enter the password directly after-p, which is more secure Enter password: # enter the password Welcome to the MariaDB monitor. Commands end with; or\ g.Your MariaDB connection id is 24Server version: 10.2.9-MariaDB-log MariaDB ServerCopyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.MariaDB [(none)] > show databases # display database file information And we can see from "[(none)]" that we are not in any particular database +-+ | Database | +-+ | # mysql50#.mozilla | | information_schema | | mysql | | performance_schema | +- -+ 4 rows in set (0.01 sec) MariaDB [(none)] > use mysql # setting specifies that the database is the current database (Note: here is an internal command for mysql There is no need to add a semicolon after the semicolon, all the semicolons are SQL statements) Database changedMariaDB [mysql] > show tables # display all table information in the current database +-+ | Tables_in_mysql | +-+ | column_stats | | columns_priv | | db | | event | | func | | general_log | | gtid_slave_pos | | help_category | | help_keyword | | help_relation | | help_topic | | host | | index_stats | | innodb | _ index_stats | | innodb_table_stats | | plugin | | proc | | procs_priv | | proxies_priv | | roles_mapping | | servers | | slow_log | | table_stats | | tables_priv | | time_zone | | | time_zone_leap_second | | time_zone_name | | time_zone_transition | | time_zone_transition_type | | user | +-+ 30 rows in set (0.01 sec) MariaDB [mysql] > desc user | # View the table structure of user table Name, type, value, .. (the content only shows the previous part) +-+ | Field | | Type | Null | Key | Default | Extra | +-+ | Host | | | char (60) | NO | PRI | | User | char (80) | NO | PRI | | Password | char (41) | NO | | Select_priv | enum ('N') | 'Y') | NO | | N | | Insert_priv | enum (' N') 'Y') | NO | | N | | +-+-- -- + 46 rows in set (0.01sec) MariaDB [mysql] > select host User,password from user # find host/user/password information from the user table (as shown in the following figure) +-+-+ | host | user | password | | +-- + | localhost | root | * 367DDCBB0F311F04DDA795BC971B23954EF636DD | | localhost.localdomain | root | * 367DDCBB0F311F04DDA795BC971B23954EF636DD | | 127.0.0.1 | Root | * 367DDCBB0F311F04DDA795BC971B23954EF636DD |:: 1 | root | * 367DDCBB0F311F04DDA795BC971B23954EF636DD | +-- + 4 rows in set (0.00 sec) MariaDB [mysql] > select user () # check the current user name +-+ | user () | # Note: the user name in mysql is different from the usual user name. The user name in +-+ mysql is the full name formed by the combination of "user name @ local (or ip address)" | root@localhost | +-+ 1 row in set (0.00 sec) # Note: for SQL statements, add a semicolon, otherwise enter will not be executed Until you enter the semicolon (shown below) MariaDB [(none)] > show databases->-> +-+ | Database | +-+ | # mysql50#.mozilla | | information_schema | | mysql | | performance_schema | +-+ 4 rows in set (0.00 secMariaDB [mysql] > quit # quit exit Bye [root@localhost / usr/local/mysql] #

Five: summary of the experiment

In fact, the step of this experiment is very simple, mainly to OK the code correctly. In the experiment, some students typed the three lines of code in the configuration file my.conf incorrectly, resulting in some problems. Do not do it step by step, and then follow the steps to troubleshoot when something goes wrong.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.