MySQL Master-slave configuration

1. mysql master-slave principle and application scenario MySQL's replication principle is very simple, summed up:
Only one master can be set from each.
After the master executes the SQL, log the binary log file (Bin-log).
From the connection master, and get Binlog from the master, save in local relay-log, and execute SQL from the location you last remembered, and stop synchronizing once an error is encountered.
Judging from these replication principles, these inferences can be inferred:
Master-slave database is not real-time synchronization, even if the network connection is normal, there is an instant, master-slave data inconsistency.
If the master-slave network is disconnected, from the network after normal, batch synchronization.
If you modify the data from, then it is very likely from the execution of the main Bin-log error and stop synchronization, this is a very dangerous operation. So in general, be very careful to modify the data from above.
A derivative configuration is a dual-master, mutual primary from the configuration, as long as the two sides of the modification does not conflict, can work well.
If you need a multi-master, you can use a ring configuration so that any node changes can be synchronized to all nodes.

Can be applied in a read/write separation scenario to reduce I/O for a single MySQL server
Ha clusters that can implement MySQL services
Can be 1 master multiple from, can also be mutual master and slave (master)

Note: The following document is configured on the same machine, two MySQL services, in our class, we will use two separate machines to demonstrate, but the steps are basically the same.


2. Install and configure MySQL
Refer to previous steps to build MySQL service
To make the experiment convenient, we configured two MySQL services on the same machine (running two ports)
cd/usr/local/; Cp-r MySQL mysql_2; CD mysql_2;
Initialize MYSQL2:./scripts/mysql_install_db--user=mysql--DATADIR=/DATA/MYSQL2
Copy configuration file: Cp/etc/my.cnf./my.cnf
Modify profile related parameters: Vim my.cnf #更改port and socket
Start:/usr/local/mysql_2/bin/mysqld_safe--defaults-file=/usr/local/mysql_2/my.cnf--user=mysql &
If you boot it, you need to add it to the/etc/rc.local.

3. Configure Master-Slave preparation
Set Mysql_2 to primary port 3307,mysql to 3306 from port
Create a test library in our Lord: Creation database db1;
Then export the master MySQL library data and import it to DB1
Mysqldump-uroot-s/tmp/mysql2.sock mysql > 123.sql;
Mysql-uroot-s/tmp/mysql2.sock DB1 < 123.sql

4. Configuration Master (Master)
VIM/USR/LOCAL/MYSQL_2/MY.CNF #修改或添加:
Server-id=1
Log-bin=mysql-bin
Two optional parameters (2 Select 1):
BINLOG-DO-DB=DB1,DB2 #需要同步的库
BINLOG-IGNORE-DB=DB1,DB2 #忽略不同步的库
After modifying the configuration file, restart Mysql_2
Pid= ' PS uax |grep mysql2.sock |grep-v grep |awk ' {print $} '; Kill $pid; cd/usr/local/mysql_2/bin/;/mysqld_safe--defaults-file=. /MY.CNF--user=mysql &
Set root password: mysqladmin-u root-s/tmp/mysql2.sock password ' 123456 '
Mysql-u root-s/tmp/mysql2.sock-p ' 123456 '
Grant Replication Slave on * * to ' repl ' @ ' 127.0.0.1 ' identified by ' 123123 ';

Flush privileges; (Refresh again)

flush tables with read lock; (Lock Dead)
Show master status; # Be sure to remember the first two columns, one will use

5. Set from (slave)
VIM/ETC/MY.CNF #修改或增加
Server-id = 2 #这个数值不能和主一样
Optional parameters: Replicate-do-db=db1,db2 and Replicate-ignore-db=db1,db2 #意义同主的那两个可选参数
Service mysqld Restart
Copy the master's DB1 library data to from: Mysqldump-uroot-s/tmp/mysql2.sock-p123456 db1 > db1.sql; Mysql-uroot-p-E "CREATE database DB1"; Mysql-uroot-p DB1 < Db1.sql
Mysql-uroot-p #登陆从的mysql
Slave stop;
Change Master to master_host= ' 127.0.0.1 ', master_port=3306, master_user= ' repl ', master_password= ' 123123 ', Master_log_ File= ' mysql-bin.000006 ', master_log_pos=474952;

/etc/init.d/mysqldslave restart
Slave start;
Lord: Mysql-uroot-s/tmp/mysql2.sock-p123456-e "Unlock Tables"
View from the status from: Show slave status\g; (two OK to succeed)

6. Test Master and slave

unlock tables; (unlock)
The Lord emptied the DB table of the DB1 library: use DB1; Select COUNT (*) from DB "; TRUNCATE TABLE db;
Enter slave to view the DB1 Library DB table: use DB1; Select COUNT (*) from DB ";
Lord Delete table Db:drop tables db;
From the top, the DB table doesn't exist.
Recommendation: MySQL master-slave mechanism is relatively fragile, cautious operation. If you restart Master, be sure to stop the slave first, that is, you need to slave up the slave Stop command, and then go to restart the master MySQL service, otherwise it is likely to be interrupted. Of course, after the restart, you also need to slave to open slave start.

MySQL Master-slave configuration