MySQL Master-slave replication

The master-slave configuration of the MySQLServer allows for read-write separation and 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, but also can start multiple services on one server.Host a:192.168.1.100

Slave machine b:192.168.1.101

Can have more than one slave machine

1, first log on to host A, on the primary server, set up an account from the database, using REPLICATION SLAVE (copy from)Give permissions, such as:
Mysql>grant REPLICATION SLAVE on * * to ' backup ' @ ' 192.168.1.101 ' identified by ' 123456 ';
Given slave permissions, there are multiple slave machines that run multiple times.

2. Open the my.cnf of host A, enter for example the following: (Change the primary database configuration file MY.CNF. Turn on Binlog. and sets the value of the Server-id. The MySQL service must be restarted after the change)
server-id = 1 #主机标示, integer
log_bin   =/var/log/mysql/mysql-bin.log #确保此文件可写, open bin-log.
Read-only =0#主机, both read and write can
Binlog-do-db=test#须要备份数据, more than one write multiple lines
Binlog-ignore-db=mysql #不须要备份的数据库. Multiple lines can be written by mysql>show variables like ' log_% '; Verify that the binary logs are started.

3, now be able to stop the master Data Update operation, and generate a backup of the primary database, we can through the mysqldump everywhere data to from the database, of course. You can also directly use the CP command to copy the data files to the database, note that before exporting the data to the primary database read LOCK, to ensure data consistency
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 >/home/chenyz/test.sql

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

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 backup is complete.

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, change from the database my.cnf, add Server-id parameters. Specifies the user that replication uses, the ip,port of the primary database server, and the file and location where the replication log starts. Open the my.cnf from machine B, enter
server-id 12> = 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 #假设从server发现主server断掉. Time difference of another connection (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 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

Can be in host a. Do some insert, UPDATE, DELETE operations to see if Host B has been altered.

MySQL Master-slave replication