MySQL database master-slave configuration and restore

Primary database server Configuration Master 51

Master from the configuration to maintain two databases in the database and table consistent

1 Doing user authorization
2 Enable Binlog logging
3 Viewing the Binlog log file in use
1.
mysql> grant replication Slave on . (All libraries and tables)
→
-e [email protected] "%" available% for all, also can specify specific IP address
-Identified by "123456"; Specify password
2.
] #vim/etc/my.cnf Modify the configuration file
[Mysqld]
Server_id=51 specifying the server host name
Log-bin=master51
binlog-format= "Mixed"
: Wq
] #systemctl Restart mysqld Restart mysqld service
] #ls/var/lib/mysql/master51 to see if/var/lib/mysql/has a new file generation that starts with master51
3.
Mysql> Show master status; View the status of the primary server, with the result being successful

Configuration from the database server slave 52
1 Verifying the primary library authorized user
2 Specifying SERVER_ID
3 Specifying the main library information
4 Viewing configurations

1.
] #mysql-h192.168.4.51-urepluser-p123456 Verify the Primary library authorization, log on to host 51 database from host 52

2.
] #vim/etc/my.cnf Modify the configuration file
[Mysqld]
server_id=52
: Wq
]# systemctl Restart mysqld Restart Service

3.
]# mysql-uroot-p123456
Mysql> show Slave status\g; View from configuration information

Mysql> Change Master to
-master_host= "192.168.4.51", IP address
-Master_user= "Repluser", User name
-master_password= "123456", password
-master_log_file= "master51.000001", Binlog log
master_log_pos=154; Offset amount

mysql> start slave; Start the slave process

4.
Mysql> show Slave status\g; View the status from the server, Io/sql thread status is yes for success
Slave_io_running:yes IO Thread
Slave_sql_running:yes SQL Thread

Verifying master-Slave synchronization configuration
Create a new library on the main library server new tables insert records with the same data from the library.

Master-Slave synchronization working principle
Slave_io_running:yes
Slave_sql_running:yes

Troubleshooting:
IO thread: The SQL command that binlog the log in the main library is recorded in the trunk log file of the machine.
IO thread Error Reason: Specify main library information error, FIRWALLD, SELinux
Last_io_error: Check the cause of the error

Repair IO thread?
mysql> stop Slave;
mysql> change Master to option = value;
mysql> start slave;

SQL thread: Executes the SQL command in the trunk log file to write the data into the native library
SQL thread error Reason: Execute SQL command in trunk log file with library or table does not exist in native
Last_sql_error: Check the cause of the error

Fix the SQL thread so that it has a library or table on the main library stop slave; Start slave;

Restores:
Restore the database server 192.168.4.X to a separate database server.

To get to the/var/lib/mysql directory first.
]# RM-RF master.info Delete master-slave configuration files
RM-RF Relay-log.info
RM-RF Host name-relay-bin.*
Comment out the conditions set in the VIM/ETC/MY.CNF configuration file
]# systemctl Restart mysqld Restart Service
Mysql> show Slave status\g; View status

MySQL database master-slave configuration and restore