MySQL主从复制实践与部署
本文主要介绍在单机单数据库多实例的环境下，实现MySQL主从复制功能基本流程：
1.MySQL主从复制介绍
2.主从复制数据库实战环境准备
3.在主库Master上执行操作配置
4.在MySQL从库上执行的操作过程
5.启动从库同步开关，测试主动复制配置情况
6.MySQL主从复制配置步骤总结
流程一：MySQL主从复制介绍
Mysql数据库的主从复制方案，与使用scp/rsync等命令进行的文件级别复制类似，都是数据的远程传输，只不过Mysql的主从复制是其自带的功能，无需借助第三方工具，而且，Mysql的主从复制并不是数据库磁盘上的文件直接拷贝，而是通过逻辑的binlog日志复制到要同步的服务器本地，然后由本地的线程读取日志里面的SQL语句，重新应用到Mysql数据库中。
Mysql的主从复制是一个异步的过程（虽然一般情况下感觉是实时的），数据将从一个Mysql数据库（我们称之为Master）复制到另一个Mysql数据库（我们称之为Slave），在Master和Slave之间实现整个主从复制的过程是由三个线程参与完成的。其中有两个线程（SQL线程和I/O线程）在Slave端，另外一个线程（I/O线程）在Master端。要实现Mysql的主从复制，首先必须打开Master端的binlog记录功能，否则就无法实现，要打开MySQL的binlog记录功能，可通过在MySQL的配置文件my.cnf中mysqld模块（标识后的参数部分）增加“log-bin”参数选项来实现。因为整个复制过程实际上就是Slave端从Master端获取binlog日志，然后再在Slave上以相同顺序执行获取的binlog日志中所记录的各种SQL操作。
MySQL主从复制原理的重点总结：
#主从复制是异步的逻辑的SQL语句级的复制。
#复制时，主库有一个I/O线程，从库有两个线程，即I/O和SQL线程。
#实现主从复制的必要条件是主库要开启记录binlog功能。
#作为复制的所有MySQL节点的server_id都不能相同。
#binlog文件只记录对数据库有更改的SQL语句（来自主数据库内容的变更），不记录任何查询（如select，show）语句。
流程二：主从复制数据库实战环境准备
1）主从复制实践准备
本文以单机数据库多实例的环境为例讲解。配置MySQL多实例可参考http://blog.51cto.com/13707680/2112502
实例端口信息如下：
# netstat -tnlp | grep 330
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 8675/mysqld
tcp 0 0 0.0.0.0:3307 0.0.0.0:* LISTEN 8271/mysqld
tcp 0 0 0.0.0.0:3308 0.0.0.0:* LISTEN 9256/mysqld
2）定义主从复制的服务器角色
主库（mysql master）: 【ip为192.168.1.72port 为 3306】
从库（mysql slave） : 【ip为192.168.1.72port 为 3307】
从库（mysql slave） : 【ip为192.168.1.72port 为 3308】
流程三：在主库Master上执行操作配置
1）设置server_id值并开启binlog功能参数
# egrep "server_id|log_bin" /data/3306/my.cnf
log_bin=/data/3306/mysql-bin
server_id=3306 #用于同步的每台机器或实例server_id都不能相同
上面这个两个参数一定在放在mysqld模块下，否则会出错。
2）重启主库MySQL服务，命令如下：
# /data/3306/mysqld restart
mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'root'@'localhost' (using password: NO)'
这里报错说要密码，修改下启动脚本文件
# grep Stop= /data/3306/mysqld
Stop='mysqladmin -uroot -pywxi123 -S /data/3306/mysql.sock shutdown'
# /data/3306/mysqldrestart
MySQL already Stoped
MySQL 3306 Starting
3）登录数据库，检查参数的更改情况，如下：
# mysql -uroot -pywxi123 -S /data/3306/mysql.sock
Welcome to the MySQL monitor.Commands end with ; or \g.
Your MySQL connection> Server version: 5.5.32-log MySQL Community Server (GPL)
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 3306|
+---------------+-------+
1 row in set (0.01 sec)
mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | ON |
+---------------+-------+
1 row in set (0.00 sec)
#这样binlog功能就正常开启了。
4）在主库上建立用于主从复制的账号
mysql -uroot -pywxi123 -S /data/3306/mysql.sock
grant replication slave on *.* to 'rep'@'192.168.1.%'> flush privileges; #刷新权限，是授权的权限生效
检查主库创建的rep复制账号命令及结果如下：
mysql> select user,host from mysql.user where user='rep';
+------+-------------+
| user | host |
+------+-------------+
| rep| 192.168.1.% |
+------+-------------+
1 row in set (0.00 sec)
mysql> show grants for rep@'192.168.1.%';
+--------------------------------------------------------------------------------------------------------------------------+
| Grants for rep@192.168.1.% |
+--------------------------------------------------------------------------------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO 'rep'@'192.168.1.%'> +--------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
5）实现对主数据库锁表只读
mysql> flush table with read lock;
Query OK, 0 rows affected (0.00 sec)
锁表后查看主库状态：
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000006 | 334 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
#锁表这个操作就是为了更好的导出和备份数据
# mkdir -p /server/backup
# mysqldump -uroot -p'ywxi123' -S /data/3306/mysql.sock --events -A -B |gzip > /server/backup/mysql_bak.$(date +%F).sql.gz
#导出数据完毕后，解锁主库，恢复可写，命令如下：
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
流程四：在MySQL从库上执行的操作过程
1）设置server_id值并关闭binlog功能参数
如果从库不做级联复制，并且不做复制用，就不要开启binlog，开启了反而会增加从库磁盘I/O等的压力。
# egrep "server_id|log_bin" /data/3307/my.cnf
#log_bin=/data/3307/mysql-bin
server_id=3307 #ID是唯一的
2）重启3307的从数据库
# /data/3307/mysqld stop
# /data/3307/mysqld start
3）登录数据库检查参数的改变情况
mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | OFF |
+---------------+-------+
1 row in set (0.00 sec)
mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 3307|
+---------------+-------+
1 row in set (0.00 sec)
4)把从主库mysqldump导出的数据恢复到从库
操作命令如下：
cd /server/backup/
mysqldump -uroot -p'ywxi123' -S /data/3307/mysql.sock --events -A -B |gzip > /server/backup/mysql_bak.$(date +%F).sql.gz
5）在3307从库上配置复制参数
# mysql -uroot -pywxi123 -S /data/3306/mysql.sock -e "show master status"
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000006 | 334 | | |
+------------------+----------+--------------+------------------+
# mysql -uroot -pywxi123 -S /data/3307/mysql.sockMASTER_PORT=3306,
> MASTER_USER='rep',
> MASTER_PASSWORD='ywxi123',
> MASTER_LOG_FILE='mysql-bin.000006',
> MASTER_LOG_POS=334;
> EOF
mysql -uroot -S /data/3308/mysql.sock -e "start slave;"
mysql -uroot-S /data/3308/mysql.sock -e "show slave status\G;"|egrep "IO_Running|SQL_Running|_Behind_Master"
流程六：MySQL主从复制配置步骤总结
MySQL主从复制配置完整步骤如下：
1）准备两台数据库或者单台多实例环境，确定能正常启动和登录即可
2）配置my.cnf文件：主库配置log_bin和server_id参数；从库配置server_id，这个值不能与主库一样是唯一的，一般不建议开启log-bin功能。配置参数后一定要重启服务生效
3）登录主库，增加从库连接主库同步的账户，例如：rep，并授权replication slave同步的权限
4）登录主库，整库锁表flush table with read lock (窗口关闭后即失效，超时参数设置的时间到了，锁表也失效)，然后show master status 查看binlog的位置状态
5）新开窗口，在linux命令行备份导出原有的数据库数据，并拷贝到从库所在的服务器目录。如果数据库数据量很大，并且允许停机，可以停机打包，而不用mysqldump
6）导出主数据后，执行unlock tables解锁主库
7）把主库导出的数据恢复到从库
8）根据主库的show master status 查看到的binlog的位置状态，在从库执行change master to.....语句。
9）从库开启复制开关，即执行start slave；（要在change语句之后执行，不然插入不了语句）
10）从库show slave status\G，检查同步状态，并在主库进行更新测试。
页:
[1]