Shulou(Shulou.com)06/01 Report--

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.