MySQL主从复制实践与部署

hyperv

　　本文主要介绍在单机单数据库多实例的环境下，实现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模块（[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
　　实例端口信息如下：
　　[root@localhost 3308]# 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.72  port 为 3306】
　　从库（mysql slave）   : 【ip为192.168.1.72  port 为 3307】
　　从库（mysql slave）   : 【ip为192.168.1.72  port 为 3308】
　　流程三：在主库Master上执行操作配置
　　1）设置server_id值并开启binlog功能参数
　　[root@localhost 3306]# egrep "server_id|log_bin" /data/3306/my.cnf
　　log_bin=/data/3306/mysql-bin
　　server_id=3306   #用于同步的每台机器或实例server_id都不能相同
　　上面这个两个参数一定在放在mysqld模块下，否则会出错。
　　2）重启主库MySQL服务，命令如下：
　　[root@localhost 3306]# /data/3306/mysqld restart
　　mysqladmin: connect to server at 'localhost' failed
　　error: 'Access denied for user 'root'@'localhost' (using password: NO)'
　　这里报错说要密码，修改下启动脚本文件
　　[root@localhost 3306]# grep Stop= /data/3306/mysqld
　　Stop='mysqladmin -uroot -pywxi123 -S /data/3306/mysql.sock shutdown'
　　[root@localhost 3306]# /data/3306/mysqld  restart
　　MySQL already Stoped                                       [  OK  ]
　　MySQL 3306 Starting                                        [  OK  ]
　　3）登录数据库，检查参数的更改情况，如下：
　　[root@localhost 3306]# 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)
　　#锁表这个操作就是为了更好的导出和备份数据
　　[root@localhost 3307]# mkdir -p /server/backup
　　[root@localhost 3307]# 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等的压力。
　　[root@localhost 3306]# egrep "server_id|log_bin" /data/3307/my.cnf
　　#log_bin=/data/3307/mysql-bin
　　server_id=3307   #ID是唯一的
　　2）重启3307的从数据库
　　[root@localhost 3307]# /data/3307/mysqld stop
　　[root@localhost 3307]# /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从库上配置复制参数
　　[root@localhost backup]# mysql -uroot -pywxi123 -S /data/3306/mysql.sock -e "show master status"
　　+------------------+----------+--------------+------------------+
　　| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
　　+------------------+----------+--------------+------------------+
　　| mysql-bin.000006 |      334 |              |                  |
　　+------------------+----------+--------------+------------------+
　　[root@localhost backup]# mysql -uroot -pywxi123 -S /data/3307/mysql.sock  MASTER_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，检查同步状态，并在主库进行更新测试。