MySQL Master-slave replication

The master-slave configuration of the MySQL server, which allows for read-write separation, or can be recovered from the standby after the main library has been hung out.

Requires two machines, install MySQL, two machines in a connected LAN, can be distributed on different servers, you can also start multiple services on a single server.

Host a:192.168.1.500

slave b:192.168.1.501

can have multiple slave

1, log on to host a first, set up an account from the database on the primary server, use REPLICATION SLAVE (copy) grant permissions, such as:
Mysql>grant REPLICATION SLAVE on * * to ' user ' @ ' 192.168.1.501 ' identified By ' 123456 '; The
gives the slave permission, which is executed multiple times, with multiple slave machines.

2, open Host A's my.cnf, enter as follows: (Modify the primary database configuration file my.cnf, turn on Binlog, and set the value of Server-id, after the modification must restart the MySQL service)
server-id               = 1     #主机标示, Integer
log_bin                  =/var/log/mysql/mysql-bin.log   #确保此文件可写, open bin-log
read-only               =0  #主机, reading and writing can be
Binlog-do-db          =test   #需要备份数据, multiple write multiple lines
binlog-ignore-db     =mysql #不需要备份的数据库, multiple write multiline

Can be through mysql>show variables like ' log_% '; Verify that the binary logs are started.

3, can now stop the main data update operation, and generate a backup of the primary database, we can mysqldump data from the database, of course, you can also directly use the CP command to copy the data file to the database, note that before exporting the data to the primary database read LOCK, To ensure consistency of data
Mysql> flush tables with read lock;

Query OK, 0 rows affected (0.19 sec)

Then mysqldump export the data:

Mysqldump-h127.0.0.1-p3306-uroot-p Test >/tmp/test.sql

4, the primary server is the current binary log name and offset, the purpose of this operation is to start from the database, starting from this point of data recovery.

Mysql> Show Master Status\g;

1. Row ***************************

file:mysql-bin.000003

position:243

binlog_do_db:

binlog_ignore_db:

1 row in Set (0.00 sec)

It is best to restore the write operation after the primary database has been backed up.

mysql> unlock tables;

Query OK, 0 rows affected (0.28 sec)

5. Copy the Test.sql from the primary data backup to the database and import it.

6, modify the MY.CNF from the database, increase the Server-id parameter, specify the user that replication uses, the IP of the primary database server, the port, and the file and location where the replication log begins to execute. Open the my.cnf from machine B, enter
Server-id = 2
Log_bin =/var/log/mysql/mysql-bin.log
Master-host =192.168.1.100
Master-user =backup
Master-pass =123456
Master-port =3306
Master-connect-retry=60 #如果从服务器发现主服务器断掉, reconnection time difference (seconds)
Replicate-do-db =test #只复制某个库
Replicate-ignore-db=mysql #不复制某个库

7. On the slave server, start the slave process
mysql> start slave;

8. Show Salve status verification from the server
mysql> SHOW SLAVE Status\g

1. Row ***************************

Slave_io_state:waiting for Master to send event

Master_host:localhost

Master_user:root

master_port:3306

Connect_retry:3

master_log_file:mysql-bin.003

read_master_log_pos:79

relay_log_file:gbichot-relay-bin.003

relay_log_pos:548

Relay_master_log_file:mysql-bin. 003

Slave_io_running:yes

Slave_sql_running:yes

9. Verification

In host A, mysql>show master status\g;

In slave B, mysql>show slave status\g;

Can see roughly this content

file:mysql-bin.000001
position:1374
Binlog_do_db:test
Binlog_ignore_db:mysql

You can do some insert, UPDATE, DELETE operations in host A to see if the Host B has been modified.

MySQL Master-slave replication