Mysql Master and slave settings

First, prepare the server

Because the Binlog format may be different between versions of MySQL (binary log), the best combination is that the MySQL version of Master and the slave version are the same or lower, and the master version must not be higher than the slave version.

I tested the server to set the success:
The version of the master server (hereinafter referred to as Master) is 3.23.58,
From the server (hereinafter referred to as slave) version is 5.0.91, this span should be relatively large bar.

The master host name is: A (ip:192.168.0.1),
The Slave host name is: B (ip:192.168.0.2),
The 2 MySQL basedir directories are all/usr/local/mysql,datadir:/var/lib/mysql/.
This is the default installation.

Second, set the synchronization master server

1. Set MY.CNF

# CP/USR/SHARE/DOC/MYSQL-SERVER-3.23.58/MY-LARGE.CNF/ETC/MY.CNF
For my-large.cnf This file personal feel is only MySQL large database configuration of the demo file, copy the directory under the MY-SMALL.CNF should be the same;
Because my server configuration is relatively high, see large inside parameters, easy to choose his use.
To modify the My.cnf file:

Log-bin = mysql-bin        #指定生成binlog文件名
server-id        = 1            #此id不可重复
binlog-do-db = news        #需要备份的数据库名, if you back up multiple databases, set this option repeatedly
Binlog-ignore-db = mysql  #不需要同步的数据库名, you can set multiple
Binlog-ignore-db = test     #不需要同步的数据库名, you can set multiple lines
Log-slave-updates             #这个参数一定要加上, otherwise updates will not be recorded in the binary file
slave-skip-errors            #是跳过错误, continue with the copy operation

2. Establish user
$mysql-U root-p #进入mysql
Mysql>create USER to ' backup ' @ ' percent ' dentified by ' backuppass '; #创建备份用户backup
Mysql>grant file,select,reload on * * to ' backup ' @ '% '; #设置权限
Because the MySQL version is too old, so do not support replication SLAVE, otherwise use the following:
Mysql>grant file,select,reload,replication SLAVE on * * to ' backup ' @ '% '; #设置权限

Mysql>flush privileges;

3, before the completion of replication, master and slave data content must be consistent, so before the backup export database lock into read-only.
MySQL > FLUSH TABLES with READ LOCK;

4, need to synchronize the news database export, you can use mysqldump or tar to compress the way.

If you use the Mysqldump method:
$mysqldump-u root-p news > News.sql #导出数据库成文件news. sql

Because the 2 data versions are different, and the storage encoding is different, I use the above method.

If compression is used:
cd/var/lib/mysql/#mysql库目录
Tar ZCVF news.tar.gz News

5. After exporting the database, remove the read-only:

Mysql>unlock TABLES;

6, restart MySQL, the previous set of MY.CNF effective; After a reboot, the Add, update, delete, and so on on the master server are recorded in the Binlog file

$/etc/init.d/mysql Restart or service mysqld restart

7. Record the replication start breakpoint
Execute the following command to record the breakpoint that started the replication from the server, including the log name and offset.
mysql> SHOW MASTER STATUS;
Like what:
Mysql> Show Master Status\g
file:mysql-bin.001
position:73
binlog_do_db:
binlog_ignore_db:
The log file name is: mysql-bin.001
Offset is: 73
Note that the copy will start from this location from the server. You need to keep a record of these 2 places.

Second, set up the synchronization slave server

1. Import data to Slave server:
I'm using $mysql-u Root-p News < News.sql.

2, if the method is compressed:
Transfer to the main library packet, unpack
# cd/var/lib/mysql/
# SCP 192.168.0.1:/var/lib/mysql/news.tar.gz.
# tar ZXVF var.tar.gz
View Modify News folder permissions
# Chown-r Mysql:mysql News

3, set my.cnf for synchronization slave
server-id       = 2 
# log-bin=mysql-bin     #我没有用到, you only need to sync from a to B
master-host     =   192.168.0.1
Master-user      =   backup
Master-password =  backuppass
master-port      =  3306
#上面是前面添加的用户名和密码, the following 3 lines are the new version of SQL comes with the table.
Replicate-ignore-db = mysql       #忽略掉的数据库
replicate-ignore-db = test            #忽略掉的数据库
Replicate-ignore-db = information_schema  #忽略掉的数据库
Replicate-do-db = news  #需要备份的数据库名
replicate_do_table = news.user   #需要备份的数据表名
master-connect-retry=60  #如果从服务器发现主服务器断掉, the time difference of reconnection (seconds)
#log-slave-updates  #我没有用到 If you want to update the records from the server to the binary files
slave-skip-errors  #是跳过错误 to continue with the copy operation

After the setup is complete, first check the/var/lib/mysql/directory for MySQL's startup shutdown log, similar to the log information for *.info,localhost*,ib*. If all is removed (hint: Stop the MySQL service first, then delete), then start the MySQL service.

4. Verify Connection Master
# Mysql-h192.168.0.1-ubackup-pbackuppass
Host IP, user name, password cannot be preceded by a space

5, set the master server information, let slave know the location of master

The way it used to be is to set the MASTER's message directly in the MY.CNF; however, this has changed and must be done with the command "Change MASTER to" under MySQL

Master is wrong in my.cnf.

Master-host = 192.168.0.1
Master-user = Backup
Master-password = BackupPass
Master-port = 3306

Slave stop;
Mysql> Change MASTER to
Master_host= ' 192.168.0.1 ',
-Master_user= ' backup ',
Master_password= ' BackupPass ',
Master_log_file= ' mysql-bin.001 ',
master_log_pos=73;
Slave start;

6. Check if synchronization is successful
Use show slave status; Take a look at the synchronization from the server
Slave_io_running:yes
Slave_sql_running:yes
If all is yes, that means it's already in sync.

Whether the Seconds_behind_master is 0,0 is already synchronized.

A slave_io_running:no appears and may need to be turned on:
Slave stop;
SLAVE Start Io_thread
Slave start;

Also, there is a reason why Slave_io_running:no is not authorized to read the data on Master on slave.

Mysql Master and slave settings