MySQL Master-slave configuration

MySQL master-slave 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,
Stop syncing 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, it is possible to stop synchronizing from the error that occurred while executing the main bin-log, which
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)
Description: The following document is configured for two MySQL services on the same machine, and when we are in class, we will use a
Two separate machines to demonstrate, but the steps are basically the same.
1. Install the configuration MySQL
Go to/usr/local/src download
wget http://mirrors.sohu.com/mysql/MySQL-5.1/mysql-5.1.73-linux-i686-glibc23.tar.gz
Initialize MySQL
#tar zxvf/usr/local/src/mysql-5.1.73-linux-i686-icc-glibc23.tar.gz//Decompression
#mv Mysql-5.1.73-linux-i686-icc-glibc23/usr/local/mysql//Reposition position
#useradd-S/sbin/nologin mysql//build MySQL User
#cd/usr/local/mysql
#mkdir-P/data/mysql//Create DATADIR, the database file will be placed in this area
#chown-R mysql:mysql/data/mysql//change permissions
#./scripts/mysql_install_db--user=mysql--datadir=/data/mysql

Configure MySQL
Copy configuration file
#cp support-files/my-large.cnf/etc/my.cnf
Copy the startup script file and modify its properties
#cp Support-files/mysql.server/etc/init.d/mysqld
#chmod 755/etc/init.d/mysqld
modifying startup scripts
#vim/etc/init.d/mysqld
There is a "Basedir=/usr/local/mysql;datadir=/data/mysql" (the directory defined when the database was previously initialized) where modifications are required.

To make the experiment convenient, we configured two on the same machine
A MySQL service (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
Modifying configuration file-related parameters
#vim my.cnf
Change port to 3307 and socket to/tmp/mysql2.sock
Cp/etc/init.d/mysqld/etc/init.d/mysqld2
Vim/etc/init.d/mysqld2
Change the following:
Basedir=/usr/local/mysql_2
Datadir=/data/mysql2
conf= $basedir/my.cnf
Save exit, and then start MYSQL2
/etc/init.d/mysqld2 start

Configure Master-Slave preparation
We set the/usr/local/mysql_2 as the primary, the port 3307,/usr/local/mysql from, and the port to 3306.
Create a test library in your Lord
#mysql-uroot-s/tmp/mysql2.sock-e "CREATE 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

Configure Primary (Master)
#vim/usr/local/mysql_2/my.cnf
Modify or add:
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 $2}‘ ; kill $pid; CD
/usr/local/mysql_2/bin/;/mysqld_safe--defaults-file=. /MY.CNF--user=mysql & ##/etc/init.d/mysqld2 Restart
Set Root password
#mysqladmin-u root-s/tmp/mysql2.sock password ' A123456BC '
#mysql-U root-s/TMP/MYSQL2.SOCK-PA123456BC
Then authorize the user to synchronize the data from a REPL

Grant replication Slave on . to ' repl ' @ ' 127.0.0.1 ' identified by ' 123123 ';
Flush privileges;
Flush tables with read lock;
Show master status; #一定要记住前两列的内容, we'll use it later.

Set from (slave)

Vim/etc/my.cnf

Modify or add
Server-id = 2 #这个数值不能和主一样
Optional parameters replicate-do-db=db1,db2 and replicate-ignore-db=db1,db2, meaning the two with the master can be
Select parameters, and if the master has already defined it, then it is not necessary to add these parameters again from the top. Then restart the Mysqld service.

Service mysqld Restart

Copy the master's DB1 library data to the
#mysqldump-uroot-s/TMP/MYSQL2.SOCK-PA123456BC db1 > Db1.sql

To create a DB1 database from the top
#mysql-uroot-s/tmp/mysql.sock-e "CREATE Database DB1";
Then import the Db1.sql to the library

#mysql-uroot-s/tmp/mysql.sock DB1 < Db1.sql
Log in from MySQL
Mysql-uroot-s/tmp/mysql.sock
Execute the following command

Slave stop;
Change Master to master_host= ' 127.0.0.1 ', master_port=3307, master_user= ' Repl ',
Master_password= ' 123123 ', master_log_file= ' mysql-bin.000006 ', master_log_pos=474952;
Slave start;
And then the Lord executes
#mysql-uroot-s/tmp/mysql2.sock-p123456-e "Unlock Tables"
View status from top
show slave status\g;
See if there is a display like this
Slave_io_running:yes
Slave_sql_running:yes
Only two simultaneous yes is normal.

Test Master
In the Lord, empty the DB table of the DB1 library

Use DB1;
Select COUNT () from DB ";
TRUNCATE TABLE db;
Go to slave, view DB1 Library DB table
Use DB1;
Select COUNT () from DB;
If the result is 0, the master-slave is synchronous.
And then the Lord deletes table db
drop table db;
From the top, the DB table doesn't exist.
Select COUNT (*) from DB;

Recommendation: MySQL master-slave mechanism is relatively fragile, cautious operation. If you restart Master, you must first stop the slave,
This means that you need to execute the slave stop command on the slave and then restart the master MySQL service, otherwise
There is a good chance that it will be interrupted. Of course, after the restart, you also need to slave to open slave start.

MySQL Master-slave configuration