Master-Slave configuration and restore of mysql Database
Configuration of the primary database server master 51
Master-slave configuration should keep the libraries and tables in the two databases consistent.
1 do user authorization
2 enable binlog Log
3 View the binlog log files that are in use
1 、
Mysql > grant replication slave on. Authorization. (all libraries and tables)
-> to
-> repluser@ "%" available% represents all, or you can specify a specific ip address
-> identified by "123456"; specify password
2 、
] # vim / etc/my.cnf modify configuration file
[mysqld]
Server_id=51 specifies the server hostname
Log-bin=master51
Binlog-format= "mixed"
: wq
] # systemctl restart mysqld restart mysqld service
] # ls / var/lib/mysql/master51 check whether / var/lib/mysql/ has a new file that begins with master51
3 、
Mysql > show master status; to check the status of the master server. If there is a result, it is successful.
From the configuration of the database server slave 52
1 verify the authorized user of the main library
2 specify server_id
3 specify the main library information
4 View configuration
1 、
] # mysql-h292.168.4.51-urepluser-p123456 verify the authorization of the main library and log in to the database of host 51 from host 52
2 、
] # vim / etc/my.cnf modify configuration file
[mysqld]
Server_id=52
: wq
] # systemctl restart mysqld restart the service
3 、
] # mysql-uroot-p123456
Mysql > show slave status\ G; view slave 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
Mysql > start slave; to start the slave process
4 、
Mysql > show slave status\ G; check the status of the slave server. If the IO/SQL thread status is yes, it will be successful.
Slave_IO_Running: Yes IO thread
Slave_SQL_Running: Yes SQL thread
Verify the master-slave synchronization configuration
Create a new library on the master database server. The new table insert record has the same data in the slave database.
Working principle of master-slave synchronization
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Troubleshooting:
IO thread: put the sql commands in the binlog log of the main library into the local relay log file
Cause of IO thread error: error in specifying main database information, firwalld, selinux
Last_IO_Error: check the reason for the error
Fix IO threads?
Mysql > stop slave
Mysql > change master to option = value
Mysql > start slave
SQL thread: execute the sql command in the relay log file to write the data to the local library
Cause of SQL thread error: the library or table used to execute the sql command in the relay log file does not exist on this machine
Last_SQL_Error: check the reason for the error
Fix the SQL thread so that it has a library or table on the main library stop slave; start slave
Restore:
Restore database server 192.168.4.X to a stand-alone database server.
Go to the / var/lib/mysql directory first.
] # rm-rf master.info deletes the master-slave configuration file
Rm-rf relay-log.info
Rm-rf hostname-relay-bin.*
Comment out the conditions set in the vim / etc/my.cnf configuration file
] # systemctl restart mysqld restart the service
Mysql > show slave status\ G; View status
