1. 概述
1.1 目标
解决web应用系统，数据库出现的性能瓶颈，采用数据库集群的方式来实现查询负载；一个系统中数据库的查询操作比更新操作要多得多，通过多台查询服务器将数据库的查询分担到不同的查询服务器上从而提高查询效率。
Mysql数据库支持数据库的主从复制功能，使用主数据库进行数据的插入、删除与更新操作，而从数据库则专门用来进行数据查询操作，这样可以将更新操作和查询操作分担到不同的数据库上，从而提高了查询效率。
1.2 Mysql主从复制原理
主服务器上面的任何修改会保存在二进制日志文件Binary log里，从服务器上启动一个I/O thread进程与主服务器的I/O联系，并请求从指定日志文件的位置之后的内容。当主服务器接收到Slave服务器I/O线程请求后，通过I/O线程根据请求信息读取指定日志指定位置之后的日志信息，返回给Slave服务器的I/O线程。在返回的信息中除了日志所包含的信息之外，还有master端的binary log文件的名称和binary log中的位置。然后把读取到的二进制日志内容写到本地的一个Realy log里面，将读取到的master端的bin-log文件名和位置记录到master-info文件中。从服务器上面开启一个SQL thread定时检查Realy log，如果发现有更改立即把更改的内容在本机上面执行一遍。这样就实现了在主服务器上操作，从服务器上实时也跟着操作。
1.3 网络拓扑
2. 主数据配置
2.1 启动log-bin
[root@master ~]# vim /etc/my.cnf
[mysqld]
log-bin=/data/mysql/binlog/binlog
server-id=101
binlog-format=mixed
备注：开启log-bin日志记录，设置唯一的server id,记录日志的模式
查看log-bin
[root@testdb2 bin_log]# ls -l
total 8
-rw-rw---- 1 mysql mysql 120 Apr 30 18:33 bin-log.000001
-rw-rw---- 1 mysql mysql 39 Apr 30 18:33 bin-log.index
mysql> show variables like 'log_bin%';
+---------------------------------+---------------------------------------+
| Variable_name | Value |
+---------------------------------+---------------------------------------+
| log_bin | ON |
| log_bin_basename | /data/3307/data/bin_log/bin-log |
| log_bin_index | /data/3307/data/bin_log/bin-log.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
+---------------------------------+---------------------------------------+
5 rows in set (0.00 sec)
2.2 创建授权账号
mysql>grant replication client,replication slave on *.*
to 'repl'@'192.168.150.%'
identified by 'repl';
mysql> flush privileges;
mysql> select user,host,password from mysql.user;
+------+---------------+-------------------------------------------+
| user | host | password |
+------+---------------+-------------------------------------------+
| root | localhost | |
| root | testdb2 | |
| root | 127.0.0.1 | |
| root | ::1 | |
| | localhost | |
| | testdb2 | |
| 'repl'| 192.168.150.% | *9FF2C222F44C7BBA5CC7E3BE8573AA4E1776278C |
+------+---------------+-------------------------------------------+
7 rows in set (0.00 sec)
查看主数据库状态
mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000009 | 581 | | | |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> show master logs;
+----------------+-----------+
| Log_name | File_size |
+----------------+-----------+
| bin-log. 000009 | 581 |
+----------------+-----------+
1 row in set (0.00 sec)
2.3 备份数据库
锁表
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)
备份数据，同时记录下File和position的值
备份所有数据库
[root@testdb2 dump]# mysqldump -uroot -p -S /data/3307/mysql.sock -A -B --master-data=1 --events > full_bak_3307.sql
参数解释：
-A：全数据库备份
-B：备份脚本有创建数据库语句，例如 create database test; use test;
--master-data=1 表示会执行 CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000015', MASTER_LOG_POS=120; 这个语句，所有在从库段不要添加这两个参数 master_log_file，master_log_pos
--master-data=2 表示会注释 --CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000015', MASTER_LOG_POS=120; 这个语句
数据库文件比较大可以压缩备份
[root@testdb2 dump]# mysqldump -uroot -p -S /data/3307/mysql.sock -A -B --master-data=1 --events |gzip > full_bak_3307.sql.gz
备份完成解锁
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
3. 从数据库配置
3.1 导入所有数据库
[root@testdb2 dump]# mysql -uroot -p -S /data/3308/mysql.sock < full_bak_3307.sql
3.2 启动replaylog
[root@slave ~]# vim /etc/my.cnf
[mysqld]
server-id=111
relay-log=/data/mysql/relaylog/relaylog
[root@slave ~]# service mysqld restart
切换到主数据库
mysql> change master to
master_host='192.168.150.101',
master_port=3306,
master_user='repl',
master_password='repl',
master_log_file='binlog.000009',
master_log_pos=581;
备注：mysqldump 语句中有 --master-data=1 不需要添加，--master-data=2时需要添加。
多实例数据库制定不同的端口
change master to
master_host='192.168.150.111',
master_port=3307,
master_user='rep',
master_password='rep';
3.3 启动从数据库
[root@slave ~]#
mysql>start slave;
mysql> show slave status G; ß查看mysql主从同步是否正常
mysql> show slave status G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.150.101
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000009
Read_Master_Log_Pos: 581
Relay_Log_File: relaylog.000006
Relay_Log_Pos: 741
Relay_Master_Log_File: binlog.000009
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: 581
Relay_Log_Space: 1067
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: 101
Master_UUID: 4c469e17-b9bf-11e5-88c0-000c29f82eaa
Master_Info_File: /data/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)
备注：如果报错
mysql> start slave;
ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository
需要重置 slave 查看 Master_Info_File: /data/3308/data/master.info 是否正确
mysql> reset slave;
Query OK, 0 rows affected (0.00 sec)
IO记录日志 master.info
SQL记录的只 relay-log.info
4. 验证MySQL主从
4.1 主服务器创建数据库
mysql> create database db3;
Query OK, 1 row affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
从服务器查看
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db2 |
| db3 |
| mysql |
| performance_schema |
| relaylog |
| test |
+--------------------+
7 rows in set (0.05 sec)