前提条件：
mysql主从操作中关于mysql的安装与基本配置请参考我的另一篇博文：linux下mysql的安装以及多实例操作“http://ylcodes01.blog.51cto.com/5607366/1841686”
一、主库操作
1、1查看主库系统及mysql信息
[root@db01 ~]# cat /etc/redhat-release
CentOS release 6.7 (Final)
[root@db01 ~]# uname -r
2.6.32-573.el6.x86_64
[root@db01 ~]# mysql --version
mysql Ver 14.14 Distrib 5.5.32, for Linux (x86_64) using readline 5.1
1、2主库设置server-id以及开启binlog
[root@db01 backup]# pwd
/server/backup
[root@db01 backup]# egrep "log-bin|server-id" /etc/my.cnf
server-id = 51#以IP结尾（只适用于单实例），其他从库的server-id不要跟主库重复，当然所有从库的也不要重复，修改完一定记得重启库
log-bin=mysql-bin #主库开启binlog
1、3登录主库
[root@db01 ~]# mysql -uroot -poldboy123
1、4设置同步用户
mysql> grant replication slave on *.* to 'rep'@'10.0.0.%' identified by 'oldboy123';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> select user,host from mysql.user;
+------+-----------+
| user | host |
+------+-----------+
| rep | 10.0.0.% |
| root | 127.0.0.1 |
| root | ::1 |
| | db01 |
| root | db01 |
| | localhost |
| root | localhost |
+------+-----------+
7 rows in set (0.00 sec)
mysql> show grants for rep@'10.0.0.%';
+-----------------------------------------------------------------------------------------------------------------------+
| Grants for rep@10.0.0.% |
+-----------------------------------------------------------------------------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO 'rep'@'10.0.0.%' IDENTIFIED BY PASSWORD '*FE28814B4A8B3309DAC6ED7D3237ADED6DA1E515' |
+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
1、5主库锁表（此时不能执行事务处理）
mysql> flush table with read lock;
Query OK, 0 rows affected (0.00 sec)
1、6查看主库binlog信息
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 | 289 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
| testdb01 |
| testdb02 |
| testdb03 |
+--------------------+
7 rows in set (0.00 sec)
1、7另开窗口导出主库现有数据
[root@db01 backup]# mysqldump -uroot -poldboy123 --events -A -B|gzip>/server/backup/mysql_bak.2016-09-16.sql.gz
[root@db01 backup]# ll
total 144
-rw-r--r-- 1 root root 144465 Sep 16 17:38 mysql_bak.2016-09-16.sql.gz
1、8发送给从库
[root@db01 backup]# scp mysql_bak.2016-09-16.sql.gz root@10.0.0.52:/server/backup/
1、9主库关闭锁表状态
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
二、从库操作
2、1查看从库系统及mysql信息
[root@db02 ~]# cat /etc/redhat-release
CentOS release 6.7 (Final)
[root@db02 ~]# uname -r
2.6.32-573.el6.x86_64
[root@db02 backup]# mysql --version
mysql Ver 14.14 Distrib 5.5.32, for Linux (x86_64) using readline 5.1
[root@db02 backup]# pwd
/server/backup
2、2设置从库server-id
[root@db02 backup]# egrep "log-bin|server-id" /etc/my.cnf
server-id = 52 #以IP结尾（只适用于单实例），从库的server-id一定不要跟主库和其他库重复，修改一定要重启数据库
#log-bin=mysql-bin #从库一定不要开启binlog
2、3在从库对应解压主库发送的数据库备份文件
[root@db02 backup]# ll
total 144
-rw-r--r-- 1 root root 144465 Sep 16 17:39 mysql_bak.2016-09-16.sql.gz
[root@db02 backup]# gzip -d mysql_bak.2016-09-16.sql.gz
[root@db02 backup]# ll
total 520
-rw-r--r-- 1 root root 529834 Sep 16 17:39 mysql_bak.2016-09-16.sql
2、4从库导入主库对应数据库数据
[root@db02 backup]# mysql -uroot -poldboy123 < mysql_bak.2016-09-16.sql
[root@db02 ~]# mysql -uroot -poldboy123
2、5从库查看导入主库数据结果
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
| testdb01 |
| testdb02 |
| testdb03 |
+--------------------+
7 rows in set (0.00 sec)
2、6设置从库同步主库数据命令（主从复制关键命令）
mysql> CHANGE MASTER TO MASTER_HOST='10.0.0.51',MASTER_PORT=3306,MASTER_LOG_FILE='mysql-bin.000002',MASTER_LOG_POS=289,MASTER_USER='rep',MASTER_PASSWORD='oldboy123';
Query OK, 0 rows affected (0.03 sec)
#注意这里的MASTER_LOG_FILE='mysql-bin.000002'和MASTER_LOG_POS=289是对应上述主库在锁表时查看主库状态信息的内容，对应“1、6查看主库binlog信息”。
2、7一定要先执行上述change master再start slave
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.51
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 289
Relay_Log_File: db02-relay-bin.000002
Relay_Log_Pos: 253
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 289
Relay_Log_Space: 408
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 51
1 row in set (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
| testdb01 |
| testdb02 |
| testdb03 |
+--------------------+
7 rows in set (0.00 sec)
三、主库和从库测试
#测试主库数据是否同步到了从库
3、1主库操作
mysql> create database testdb04;
Query OK, 1 row affected (0.00 sec)
mysql> show databases like 'testdb04';
+---------------------+
| Database (testdb04) |
+---------------------+
| testdb04 |
+---------------------+
1 row in set (0.00 sec)
3、2从库查看
mysql> show databases like 'testdb04';
+---------------------+
| Database (testdb04) |
+---------------------+
| testdb04 |
+---------------------+
1 row in set (0.00 sec)
#现主从同步测试成功。