MySQL installation and master-slave Configuration

I use ssh for remote configuration. The configuration is slightly different. 1. Install MySQLsudoyuminstallmysqlmysql-serversudochgrp-Rmysqlvar

I use ssh for remote configuration. The configuration is slightly different. 1. install MySQLsudo yum install mysql-server sudo chgrp-R mysql/var/

I use ssh for remote configuration. The configuration is slightly different.
1. Install MySQL
Sudo yum install mysql-server
Sudo chgrp-R mysql/var/lib/mysql
Sudo chmod-R 770/var/lib/mysql
Sudo service mysqld start

Set the password for the first installation: mysql_secure_installation
Press enter and enter Y as prompted.
Enter the password twice and press Enter.
Enter Y as prompted
Thanks for using MySQL!

2. Create a new user test on MySQL
Mysql> grant all privileges on mysql. * to test@192.168.100.206 identified by 'mysql ';

Here, by the way, the command for canceling authorization is revoke, and then the "to" command in the statement is changed to "from" to revoke the granted permissions.

The command to delete a user is
Drop user username @ '%'
The following content is a test database operation created on MySQL.
3. MySQL Master/Slave Configuration
Grant all on *. * to user1@192.168.100.205 identified by "mysql ";
IP address of 192.168.100.205 slave (slave server)

Log on to the MySQL master server from the MySQL server
Sudo mysql-uuser1-pmysql-h192.168.100.206
4. Configure the master server my. cnf
[Mysqld]
Server-id = 1
Log-slow-queries = mysql-slow.log
Log-error = mysql. err
Log = mysql. log
Log-bin = mysql-bin
Datadir =/var/lib/mysql
Socket =/var/lib/mysql. sock
User = mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
Symbolic-links = 0

[Mysqld_safe]
Log-error =/var/log/mysqld. log
Pid-file =/var/run/mysqld. pid

Slave server my. cnf Configuration
[Mysqld]
Port = 3306
Server-id = 2
Expire_logs_day = 7
Log-slow-queries = mysql-slow.log
Log-error = mysql. err
Log = mysql. log
Datadir =/var/lib/mysql
Socket =/var/lib/mysql. sock
User = mysql

# Character-set-server = utf8

# Collation-server = utf8_general_ci this line and the previous behavior change encoding to UTF8,

# If you want to change the code, you must note that all codes must be modified.
# Disabling symbolic-links is recommended to prevent assorted security risks
Symbolic-links = 0

[Mysqld_safe]
Log-error =/var/log/mysqld. log
Pid-file =/var/run/mysqld. pid

5. mysql Server backup (master server)
# Mysqldump-uroot-pmysql test-l-F'/tmp/test. SQL'
This command does not require additional read locks for backup
-F: flush logs
-L indicates that the database can only perform read operations.

Tips: It can be restored through binlog, and flush logs plays an important role. It can also be said that flush has a great role.
You are advised to refresh the log before completing the operation.
6. MySQL recovery:
Mysql-uroot-pmysql test-v-f </tmp/test. SQL
-V: view the Import Details;
-F: skip can be used when an error occurs in the middle. Continue to execute the following statement.

Restore binary log files of binlog-file
Mysqlbinlog -- no-defaults binlog-file | mysql-uroot-pmysql
However, in most cases, some operations in the binlog need to be discarded;
You need to use different commands to meet your needs:
# Mysqlbinlog -- no-defaults mysqk-bin.000004 -- start-position = "193" -- stop-position = "280" | more
Restore data with the position of 193-280.
-- Start-position can be removed from the first restoration.

It can also be restored by time points.
For example: -- stop-date = "xxxx-xx: xx'
-- Start-date = "xxxx-xx: xx'

6. copy the files backed up on the master server to the tmp directory of the slave server.
Scp/tmp/test. SQL 192.168.100.205:/tmp/
Then, recover from the server. It is best to Reset the master before restoration;
Mysql-uroot-pmysql test </tmp/test. SQL

Mysql> show slave status \ G;
* *************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.100.206
Master_User: user1
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 262
Relay_Log_File: mysqld-relay-bin.000006
Relay_Log_Pos: 407
Relay_Master_Log_File: mysql-bin.000004
Slave_IO_Running: Yes
Slave_ SQL _Running: Yes # YES indicates that the two rows are successful.
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 262
Relay_Log_Space: 708
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_ SQL _Errno: 0
Last_ SQL _Error:
1 row in set (0.00 sec)

The following are some commands and notes used:

Mysql> insert into t1 values (4), (5), (6), (7), (8); insert