MySQL master-slave synchronous replication forDebian Operating System

MySQL replication description MySQL supports one-way and asynchronous replication. During the replication process, one server acts as the master server, and one or more other servers act as the slave server. MySQL replication Base

MySQL replication description MySQL supports one-way and asynchronous replication. During the replication process, one server acts as the master server, and one or more other servers act as the slave server. MySQL replication Base

MySQL replication Overview

MySQL supports unidirectional and asynchronous replication. One server acts as the master server during the replication process, and one or more other servers act as slave servers. MySQL replication tracks all database changes (updates, deletions, and so on) in binary logs based on the master server ). Therefore, binary logs must be enabled on the master server for replication. Each slave server receives updates recorded in its binary logs from the master server. When an slave server connects to the master server, it notifies the master server to locate the last successful update read from the server in the log. The slave server receives any updates from that time and executes the same updates on the local machine. Then block and wait for the master server to notify new updates. Backup on the slave server does not affect the master server. During the backup process, the master server can continue to process updates.

1. Test Environment Description
Master DB Server IP Address: 192.168.113.9
Slave DB Server IP Address: 192.168.113.8
System OS: Debian 5.0.7-32bit
MySQL Vsertion: 5.0.51a-24 + lenny5-log (Debian)
# Apt-get install mysql-server mysql-client
2. operation instructions on the master DB Server
(1) create a synchronized user name and the database to be synchronized
Mysql> grant replication slave on *. * to 'refreshuser' @ '192. 168.113.8 'identified by '20140901 ';
Mysql> flush privileges;

Mysql> create database testdb;
Mysql> use testdb;
Mysql> create table testuser (id int (4), name varchar (20 ));
Mysql> insert into testuser values (1, 'test1 ');
Mysql> flush privileges;
Mysql> select * from testuser;
+ ------ + ------- +
| Id | name |
+ ------ + ------- +
| 1 | test1 |
+ ------ + ------- + 1 row in set (0.00 sec)

(2) The backup is synchronized to the slave database server.
Mysql> flush tables with read lock; // you can specify a read lock.
Mysql> show master status; // obtain the binlog Log File Name and offset.
+ ------------------ + ---------- + -------------- + ------------------ +
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+ ------------------ + ---------- + -------------- + ------------------ +
| Mysql-bin.000001 | 98 | testdb | mysql |
+ ------------------ + ---------- + -------------- + ------------------ +
1 row in set (0.00 sec)
Mysql> show master status; // The status of the master database is displayed at the branch.
Mysql> show master logs; // displays master database logs.
# Mysqldump-uroot-p testdb>/home/testdb. SQL // Method 1: Back up the testdb database to be synchronized and restore it from the DB Server
# Cd/var/lib/mysql // Method 2: Decompress the package to the slave database server/var/lib/mysql/
# Tar zcvf testdb.tar.gz testdb/
# Scp-22 testdb.tar.gz root@192.168.113.8:/home/
Mysql> unlock tables; // unlock

(3) configure the my. cnf file of the master DB
# Cd/etc/mysql
# Cp my. cnf my. cnf_back
# Vim my. cnf
Bind-address = 127.0.0.1 // Add a comment before the address # comment out this line
// Find # modify the server-id as follows:
Server-id = 1 // unique identifier, as long as it is not the same as the slave server
Log_bin =/var/log/mysql/mysql-bin.log // must be turned on here, telling the master server to use binary logs to require mysql user permissions
Expire_logs_days = 10
Max_binlog_size = 100 M
Binlog_do_db = testdb // the database to be synchronized. Multiple databases can be separated by commas (,) or multiple lines can be written.
Binlog_ignore_db = mysql // ignore non-synchronous databases and write multiple rows
#/Etc/init. d/mysql restart

3. operations on the database server
(1) mysql-u repuser-p-h 192.168.113.1 // You can remotely connect to the primary DB when you test the repuser account created on the primary DB.
(2) configure the my. cnf file from the DB
# Cd/etc/mysql
# Cp my. cnf my. cnf_back
# Vim my. cnf // find # modify server-id as follows:
Server-id = 2
Master-host = 192.168.113.9 // ip address of the master database server
Master-user = repuser
Masters-password = 111111
Master-port = 3306
Master-connect-retry = 15
Replicate-do-db = testdb // the database to be synchronized. Multiple databases can be separated by commas (,) or multiple lines can be written.
Replicate-ignore-db = mysql
Log_bin =/var/log/mysql/mysql-bin.log
Expire_logs_days = 10
Max_binlog_size = 100 M

(3) debug from DB
#/Etc/init. d/mysql restart
Mysql> start slave; // start the replication thread. stop slave to stop the replication thread.
Mysql> show processlist;
State: Has read all relay log; waiting for the slave I/O thread to update it
Info: NULL
// Indicates that the slave has been connected to the master, and the log is accepted and executed.
Mysql> show slave status \ G; // The Branch displays the replication thread status.
Slave_IO_Running: Yes
Slave_ SQL _Running: Yes
// View the values of the preceding two items. If both values are Yes, the status is normal.