Running MySQL

After installing MySQL, perform the following operations to use the MySQL database. The operations include modifying the configuration file, setting environment variables, initializing the database, and starting and logging in to the database.

Modify the configuration file.

Edit the my.cnf file.

         
              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

In the my.cnf file, the file paths (including the software installation path basedir and data path datadir) are examples. user=mysql indicates the user is an OS user, that is, the user created in Creating a User Group and User.

Check that the my.cnf file is correctly modified.
1

cat /etc/my.cnf

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

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

Add MySQL to the service list.

       
            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 in /etc/init.d/mysql to mysql:mysql.

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

Set environment variables.

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

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

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

Make the environment variable settings take effect.
1

source /etc/profile
Check the environment variables.
1

env

Change the data directory permission.
1

chmod 755 /data/mysql/data/
Switch to the mysql user.
1 2

su - mysql whoami

Initialize the data directory of the database.

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

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

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 (because the OS user configured in the /etc/my.cnf file is user=mysql).

       
            ll /data/mysql/data

In the command output, the second line from the bottom contains the initial password, which will be used in 8.
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.

           
                ls /data/mysql/data
rm -rf /data/mysql/data/

Start the database.

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. The previous error message will not be displayed.

         
              service mysql start

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

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

Check the database process.
1

ps -ef | grep mysql

Check the database listening port.

         
              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.

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

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

Configure the database user name and password.

The user names and passwords in this document are for reference only. Replace them based on your actual requirements.
1. After logging in to the database, 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 other hosts in the domain.
```
create user 'root'@'%' identified by '123456';
```
3. Grant permissions.
```
grant all privileges on *.* to 'root'@'%';
flush privileges;
```
Exit the database.
Run the \q or exit command to exit the database.
```
exit
```

Use the new password to log in to the database.

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

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

Exit the database.

exit

Optional: Stop the database.

       
            service mysql stop

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

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

Check the database process.

       
            ps -ef | grep mysql

Parent topic: Porting Guide