Running MySQL

1. Edit the my.cnf file.

   1 2

   rm -f /etc/my.cnf echo -e "[mysqld_safe]\nlog-error=/data/mysql/log/mysql.log\npid-file=/data/mysql/run/mysqld.pid\n[mysqldump]\nquick\n[mysql]\nno-auto-rehash\n[client]\ndefault-character-set=utf8\n[mysqld]\nbasedir=/usr/local/mysql\nsocket=/data/mysql/run/mysql.sock\ntmpdir=/data/mysql/tmp\ndatadir=/data/mysql/data\ndefault_authentication_plugin=mysql_native_password\nport=3306\nuser=mysql\n" > /etc/my.cnf

   

   • Before executing the code, modify the following parameters based on your requirements:

     basedir: MySQL software installation directory

     datadir: MySQL data storage path

     tmpdir: path for storing MySQL temporary files

     log-error: MySQL error log file path

     pid-file: MySQL process ID file path

     socket: MySQL communication socket file path

     In this configuration, you can run the following commands to ensure that the directories exist. Otherwise, MySQL may fail to be started.

     mkdir -p /data/mysql/log
     mkdir -p /data/mysql/run
     mkdir -p /data/mysql/tmp
     mkdir -p /data/mysql/data

   • user=mysql indicates the user is an OS user, that is, the user created in Creating a User Group and User.
2. Check the my.cnf configuration file of the MySQL database server and ensure that the configuration file is correctly modified.

   1	cat /etc/my.cnf

   Expected result:

   [mysqld_safe]
   log-error=/data/mysql/log/mysql.log
   pid-file=/data/mysql/run/mysqld.pid
   [mysqldump]
   quick
   [mysql]
   no-auto-rehash
   [client]
   default-character-set=utf8
   [mysqld]
   basedir=/usr/local/mysql
   socket=/data/mysql/run/mysql.sock
   tmpdir=/data/mysql/tmp
   datadir=/data/mysql/data
   default_authentication_plugin=mysql_native_password
   port=3306
   user=mysql

3. Change the user group and user permission on the /etc/my.cnf configuration file to mysql:mysql.

   1 2	chown mysql:mysql /etc/my.cnf ll /etc/my.cnf

   If the following information is displayed, the operation is successful:

   -rw-r--r-- 1 mysql mysql 353 Sep 14 14:33 /etc/my.cnf

1
2
3

chmod 777 /usr/local/mysql/support-files/mysql.server
cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql
chkconfig mysql on

Change the user group and user permission on /etc/init.d/mysql to mysql:mysql.

1
2

chown -R mysql:mysql /etc/init.d/mysql
ll /etc/init.d/mysql

1. Add the path of the MySQL binary file to the environment variable.

   1	echo export PATH=$PATH:/usr/local/mysql/bin >> /etc/profile

   

   In the command, /usr/local/mysql/bin in the PATH parameter is the absolute path of the bin file in the MySQL installation directory. Change the path as required to match your installation.

2. Make the environment variable settings take effect.

   1	source /etc/profile

3. Check environment variables.

   1

   env

   Expected result:

   

chmod 755 /data/mysql/data/

1

mysqld --defaults-file=/etc/my.cnf --initialize

Or

1

/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf --initialize

The initial password of the database is displayed in the last but one line of the initialization command output. The password will be used in 8. Keep it secure.

After the initialization is complete, check whether the user group and user permission on the data file /data/mysql/data in the data directory is mysql:mysql (the OS user configured in the /etc/my.cnf file is user=mysql).

1

ll /data/mysql/data

Expected result:

• If MySQL is installed by using an RPM package obtained from the official website, the mysqld file is located in the /usr/sbin directory. Ensure that the directory specified in the command is correct.
• If the initialization fails, and the message "--initialize specified but the data directory has files in it." is displayed, delete the data and initialize the database again.

  1 2	ls /data/mysql/data rm -rf /data/mysql/data/

1. Start the database process.
   

   If you run the service mysql start command as the root user (su - root) to start the database service for the first time, the system displays a message indicating that the database fails to start because the mysql.log file is missing. To solve this problem, run the su - mysql command to switch to the mysql user and start the database service. The mysql.log file will be generated in the /data/mysql/log directory. Then, run the service mysql stop command to stop the database service, and start the database service as the root user.

   • If MySQL is installed using an RPM package obtained from the official website, run the following command to start the database:

     1	/usr/sbin/mysqld --defaults-file=/etc/my.cnf &

     After the command is executed, press Enter to proceed.

   • If MySQL is installed using an RPM package obtained from a mirror site or installed from the source, run one of the following commands:

     1	service mysql start

     Or

     1	mysqld --defaults-file=/etc/my.cnf &

     Or

     1	/usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf &

2. Check the database process.

   1	ps -ef | grep mysql

3. Check the database listening port.

   1 2 3

   netstat -anpt netstat -anpt | grep mysql netstat -anpt | grep 3306

   

   If the netstat command fails to be executed, install the dependencies.

   yum -y install net-tools

• Enter the initial password generated in 6 during database initialization.
• If MySQL is installed by using an RPM package obtained from the official website, the mysql file is located in the /usr/bin directory. When entering the command to log in to the database, change the mysql file path as required.

1

mysql -uroot -p -S /data/mysql/run/mysql.sock

Or

1

/usr/local/mysql/bin/mysql -uroot -p -S /data/mysql/run/mysql.sock

Expected result:

[mysql@localhost ~]$ /usr/bin/mysql -uroot -p -S /data/mysql/run/mysql.sock
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.17

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

The user names and passwords in the documents are for reference only. Replace them based on your requirements.

1. Change the password of the root user for logging in to the database.

   alter user 'root'@'localhost' identified by "123456";

2. Create a root user for all the hosts in the domain. Allow the root user to connect to the MySQL server from any host using the password 123456.

   create user 'root'@'%' identified by '123456';

3. Grant all permissions in the database to the root user and refresh the permission table of MySQL for the authorization to take effect.

   grant all privileges on *.* to 'root'@'%';
   flush privileges;

\q

exit

1

mysql -uroot -p -S /data/mysql/run/mysql.sock

Or

1

/usr/local/mysql/bin/mysql -uroot -p -S /data/mysql/run/mysql.sock

Exit the database.

\q

exit

1

service mysql stop

Or

1

mysqladmin -uroot -p123456 shutdown -S /data/mysql/run/mysql.sock

Or

1

/usr/local/mysql/bin/mysqladmin -uroot -p123456 shutdown -S /data/mysql/run/mysql.sock