Mysql master-slave synchronization deployment

 

Mysql master-slave synchronization deployment:

 

Master: 192.168.2.67

Slave: 192.168.2.211

The slave database of the master node has been running for a period of time.

 

Master:

Vim/etc/my. cnf

Server-id = 1 master id

Log-bin =/data/logbin/mysql-bin log Path and file name

# Binlog-do-db = cacti: Synchronize cacti. If this function is disabled, all other databases are synchronized except not allowed.

Binlog-ignore-db = mysql does not synchronize mysql databases. The following is the same

Binlog-ignore-db = test

Binlog-ignore-db = information_schema

 

/Usr/local/mysql/bin/mysql-uroot-p

Mysql> grant replication slave on *. * to rsync @ '192. 168.2.211 'identified by '123 ';

Mysql> flush privileges;

Mysql> flush tables with read lock;

Mysql> show master status;

Here we mainly record the values of file and position, which must be used by the slave end. As follows:

File | Position |

+ ------------------ + ----------

| Mysql-bin.000047 | 391592414

In another window, run the following:

Tar-czf/data/mysql/cacti. tgz/data/mysql/cacti

Scp/data/mysql/cacti. tgz root@192.168.2.211/data/mysql/cacti. tgz

Return to the previous window to unlock the database read-only.

Mysql> unlock tables;

 

Slave end:

Vim/etc/my. cnf

Server-id = 2 slave id, which must be greater than the master.

Save and exit.

 

/Usr/local/mysql/bin/mysqladmin-uroot-p shutdown

Tar xvzf/data/mysql/cacti. tgz/data/mysql/cacti

Chown-R mysql. mysql/data/mysql/cacti

/Usr/local/mysql/bin/mysql-uroot-p

Mysql> stop slave;

Mysql> change master

> Master_host = '1970. 168.2.67 ',

> Master_user = 'rsync': account and password created on the master node for master-slave Synchronization

> Master_password = '000000 ',

> Master_port = '123' indicates the port number used by the master client.

> Master_log_file = 'mysql-bin.000047 ', the file value recorded by the master.

> Master_log_pos = 391592414; position value recorded on the master end

Mysql> start slave;

Mysql> show slave status \ G

* *************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.2.67

Master_User: rsync

Master_Port: 3306

Connect_Retry: 30

Master_Log_File: mysql-bin.000047

Read_Master_Log_Pos: 413641446

Relay_Log_File: backter-relay-bin.000002

Relay_Log_Pos: 13433937

Relay_Master_Log_File: mysql-bin.000047

Slave_IO_Running: Yes

Slave_ SQL _Running: Yes

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: 405026116

Relay_Log_Space: 22049267

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: 148

1 row in set (0.00 sec)

 

View the preceding Slave_IO_Running: Yes and Slave_ SQL _Running: Yes. If either of them is yes, the master-slave synchronization is normal. If NO is displayed, the synchronization is faulty. You can view the Database Log Files. errors are displayed in the logs. You can troubleshoot the errors step by step.

 

This article is from the "My O & M path" blog