Shulou(Shulou.com)05/31 Report--

This article introduces how to achieve master-slave database synchronization in Mysql, the content is very detailed, interested friends can refer to, hope to be helpful to you.

1. Master server

1.1.Create a replication user with replication slave privileges.

Mysql > grant replication slave on *. * to 'repl'@'192.168.1.22' identified by' repl'

1.2.Editing my.cnf files

Vi / etc/my.cnf

Add

Server-id=1

And open the log-bin binary log file

Log-bin=mysql-bin

Note: the default server-id=1 needs to be removed.

1.3.Starting mysql

D_safe-user=mysql &

1.4. Set read lock

Mysql > flush tables with read lock

1.5. get the binlog log file name and offset

Mysql > show master status

+-+-- +-+

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | |

+-+-- +-+

| | mysql-bin.0000010 | 106 | | |

+-+-- +-+

1.6. Back up the database to be synchronized

Mysqldump test > test.sql

1.7. Unlock

Mysql > unlock tables

Second, from the server

2.1.Editing my.cnf files

Vi / etc/my.cnf

Add

Server-id=2

Note: the default server-id=1 needs to be removed.

2.2. Start the slave database

Mysqld_safe-user=mysql &

2.3. Set up the slave database accordingly

Mysql > change master to

-> master_host='192.168.1.22'

-> master_user='repl'

-> master_password='repl'

-> master_log_file='mysql-bin.0000010'

-> master_log_pos=106

2.4.Starting the slave server slave thread

Mysql > start slave

Execute the show processlist command to display the following processes:

Mysql > show processlistG

* 2. Row * *

Id: 2

User: system user

Host:

Db: NULL

Command: Connect

Time: 2579

State: Has read all relay log; waiting for the slave I/O thread to update it

Info: NULL indicates that slave is connected to master and starts to accept and execute logs

2.5. Check the status of the slave thread

Mysql > show slave status

* * 1. Row *

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.1.22

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.0000010

Read_Master_Log_Pos: 106

Relay_Log_File: centos-relay-bin.000002

Relay_Log_Pos: 529

Relay_Master_Log_File: mysql-bin.0000010

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

Relay_Log_Space: 830

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)

Verify that the configuration is correct

Execute on the slave server

Show slave statusG

Waiting for master to send event

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

If the above two lines are configured successfully for Yes at the same time

test

1. Create the user table in the master server test database

Mysql > use test

Mysql > create table user (id int)

2. View the user table from the server

Mysql > use test

Mysql > show tables like 'user'

+-+

| | Tables_in_test (user) |

+-+

| | user |

+-+

1 row in set (0.00 sec)

It indicates that the master-slave data synchronization is successful.

problem?

1. Appears when viewing slave status from the database

The slave I take O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work (or the-replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it)

The server-id in the my.cnf from the server is the same.

Solution:

Modify the server-id in my.cnf and restart the database service. My.cnf files have server-id=1 by default

Other instructions

Master server my.cnf

# the name of the database to be backed up by binlog-do-db=, which can be written to multiple lines

# binlog-ignore-db= database name that does not need to be backed up and can be written to multiple lines

My.cnf from the server

# the name of the database to be backed up by replicate-do-db=test

# databases ignored by replicate-ignore-db=mysql

# master-connect-retry=60 if the slave server finds that the master server is down, the time difference between reconnecting (seconds)

The following settings can also modify the my.cnf configuration file directly

Log-bin=mysql-bin

Master-host=192.168.1.22

Master-user=repl

Master-password=repl

Master-port=3306

Master-slave server synchronous maintenance

Due to various reasons, the master-slave data is inconsistent, and manual synchronization is carried out when the load is low.

Execute on the primary server

Mysql > flush tables with read lock

Query OK,rows affected (0.01sec)

Mysql > show master status

+-+-- +-+

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | |

+-+-- +-+

| | mysql-bin.0000011 | 260 | | |

+-+-- +-+

Execute on the slave server

First get the binary file name and offset of the current master server, and execute the command to synchronize the slave server with the master server

Mysql > select master_pos_wait ('mysql-bin.0000011','260')

+-+

| | master_pos_wait ('mysql-bin.0000011','260') | |

+-+

| | 0 |

+-+

1 row in set (0.01 sec)

After the synchronization is complete, perform unlocking on the primary server

Mysql > unlock tables

Switch between master and slave servers

When the master server fails, the slave server can be used as the master server. The steps are as follows:

1. Ensure that all slave databases have performed all updates in relay log, and that all updates have been performed in the slave server

Stop slave io_thread, check with show processlist to see if the status is Has read all relay log, indicating that the update is complete.

Mysql > stop slave io_thread

Query OK,0 affected (0.00 sec)

Mysql > show processlistG

* 2. Row * *

Id: 2

User: system user

Host:

Db: NULL

Command: Connect

Time: 4757

State: Has read all relay log; waiting for the slave I/O thread to update it

Info: NULL

2. Execute the stop slave,reset master command on the slave server and reset it to the master database

Mysql > stop slave

Query OK,0 affected (0.00 sec)

Mysql > reset master

Query OK,0 affected (0.00 sec)

3. Delete the master.info and relay-log.info files in the new master server database directory, otherwise the slave service will be followed the next time you restart.

On how to achieve master-slave database synchronization in Mysql to share here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

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.