Mysql主从复制(master-slave)实际操作案例 |
|
本文标签：Mysql,主从复制 在这一章节里， 我们来了解下如何在 Mysql 中进行用户授权及主从复制 复制代码 代码如下: msyql> mysql -uroot -p123123; msyql> select user, host, password from mysql.user; 结果如下: 复制代码 代码如下: +------------------+-----------+-------------------------------------------+ | user | host | password | +------------------+-----------+-------------------------------------------+ | root | localhost | *E56A114692FE0DE073F9A1DD68A00EEB9703F3F1 | | root | 127.0.0.1 | *E56A114692FE0DE073F9A1DD68A00EEB9703F3F1 | +------------------+-----------+-------------------------------------------+ 从如上表中看以看出 root 用户只能从本机登入 Mysql， 也就是来自 localhost 或者 127.0.0.1 现在来通过 grant 命令来添加授权用户 复制代码 代码如下: msyql> ？ grant //查看 grant 的详细用法 msyql> grant all on *.* to user1@192.168.10.2 identified by "123456"; // *.* = 所有的数据库.所有的表 //或者 msyql> grant replication slave on *.* to user2@192.168.10.% identified by "123456"; // %代表通配符 通过了 grant 命令给予了来自 192.168.10.2 的用户 user1 权限， 允许其远程登录, 如下: 复制代码 代码如下: +------------------+--------------+-------------------------------------------+ | user | host | password | +------------------+--------------+-------------------------------------------+ | root | localhost | *E56A114692FE0DE073F9A1DD68A00EEB9703F3F1 | | root | 127.0.0.1 | *E56A114692FE0DE073F9A1DD68A00EEB9703F3F1 | | user1 | 192.168.10.2 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | | user2 | 192.168.10.% | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | +------------------+--------------+-------------------------------------------+ 此时就可以在 192.168.10.2 的机器上访问 10.1 的 Mysql 了， 如下: 复制代码 代码如下: msyql> mysql -uuser1 -p123456 -h192.168.10.1;
修改主服务器 mysql 配置文件: 复制代码 代码如下: shawn@Shawn:'$ sudo vi /etc/mysql/my.cnf; /********** my.cnf **********/ [mysqld] #开启慢查询日志， 记录查询过长的 sql 语句，以便于优化 log_slow_queries = /var/log/mysql/mysql-slow.log #开启 bin-log 日志 log-bin = /var/log/msyql/mysql-bin.log 添加完成后重启 Mysql 服务 复制代码 代码如下: shawn@Shawn:'$ sudo /etc/init.d/mysql restart 现在你可以通过如下命令来查看 bin-log 日志是否成功开启 复制代码 代码如下: mysql> show variables like "%log_%"; | log_bin | ON | | log_slow_queries | ON | 如果显示为 ON， 那么就可以在 /var/log/mysql/ 文件夹看到 mysql-bin.000001 二进制文件 关于 bin-log 日志的相关操作: 复制代码 代码如下: mysql> flush logs； 此时就会多一个最新的 bin-log 日志 复制代码 代码如下: mysql> show master status; 查看最后一个 bin-log 日志， 如下: 复制代码 代码如下: +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000002 | 107 | | | +------------------+----------+--------------+------------------+ mysql> show master logs; 查看所有 bin-log 日志， 如下: 复制代码 代码如下: +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000001 | 4340 | | mysql-bin.000002 | 107 | +------------------+-----------+ mysql> reset master; 清空所有 bin-log 日志 复制代码 代码如下: shawn@Shawn:'$ mysqlbinlog /var/log/mysql/mysql-bin.000001 | more 查看 bin-log 日志内容 复制代码 代码如下: #如果有字符集问题的话可以执行: shawn@Shawn:'$ mysqlbinlog --no-defaults /var/log/mysql/mysql-bin.000001 shawn@Shawn:'$ mysqlbinlog /var/log/mysql/mysql-bin.000002 | mysql -uroot -p123123 test; shawn@Shawn:'$ mysqlbinlog /var/log/mysql/mysql-bin.000002 --start-position="193" --stop-position="398" | mysql -uroot -p123123 test; Mysql 主从复制 - 数据同步 到这一步的时候首先确保 Mysql 用户授权已经完成以及 Mysql bin-log 日志已经成功开启 并确保每台服务器的 server-id 是唯一的 再次修改主服务器(192.168.10.1)的 mysql 配置文件: 复制代码 代码如下: shawn@Shawn:'$ sudo vi /etc/mysql/my.cnf; /********** my.cnf **********/ #取消 server-id 注释符号 server-id = 1 /****************************/ #重启 Mysql 服务 shawn@Shawn:'$ sudo /etc/init.d/mysql restart 到这里， 主服务器的配置已经完成， 很简单 这次我们主要做的是让从服务器同步主服务器的数据， 同步的是将来所有对主服务做的增删改操作， 但是现有主服务器中的大量数据得先手动同步到从服务器， 操作如下: 复制代码 代码如下: #清空一下主服务器的 bin-log 日志， （可选: 保险操作， 防止主从 bin-log 日志混乱） mysql> reset master; #然后备份导出主服务器中现有的 test 数据库 shawn@Shawn:'$ mysqldump -uroot -p123123 test -l -F > /tmp/test.sql; -F = flush logs, 生成新的日志文件， 包括 bin-log 日志 -l = lock 数据库， 防止在导出的时候被写入数据， 完成后自动解锁 #完成后把文件传输给从服务器 shawn@Shawn:'$ scp /tmp/test.sql 192.168.10.2:/tmp/ #然后再查询确保一下从服务器已经成功授过权 mysql> show grants for user1@192.168.10.2\G *************************** 1. row *************************** Grants for user1@192.168.10.2: GRANT ALL PRIVILEGES ON *.* TO user1@192.168.10.2 IDENTIFIED BY PASSWORD *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 完成后， 现在我们到从服务器 (192.168.10.2) 导入现有的数据: 复制代码 代码如下: #清空一下从服务器的 bin-log 日志， （可选: 保险操作） mysql> reset master; #然后导入主服务器中现有的数据 shawn@Shawn:'$ mysqldump -uroot -p123123 test -v -f < /tmp/test.sql; -v = 查看导入的详细信息 -f = 是当中间遇到错误时， 可以 skip 过去， 继续执行下面的语句 当然你也可以用 source 命令导入 好了， 目前为止主服务器(192.168.10.1)和从服务器(192.168.10.2)现有的数据已经成功手动同步 接下来修改从服务器(192.168.10.2)的 mysql 配置文件: 复制代码 代码如下: shawn@Shawn:'$ sudo vi /etc/mysql/my.cnf; /********** my.cnf **********/ #取消 server-id 注释符号, 并修改值 server-id = 2 #取消 master-host 注释符号, 并修改值 master-host = 192.168.10.1 #取消 master-user 注释符号, 并修改值 master-user = user1 #取消 master-password 注释符号, 并修改值 master-password = 123456 #取消 master-port 注释符号, 并修改值, 主服务器默认端口号为: 3306 master-port = 3306 /****************************/ #重启 Mysql 服务 shawn@Shawn:'$ sudo /etc/init.d/mysql restart 配置文件修改完成， 此时在从服务器中登入自己的 Mysql, 而不是远程登入主服务器(192.168.10.1) 复制代码 代码如下: #在从服务器中登入自身的 Mysql msyql> mysql -uroot -p123123; #查看是否已经取得同步 msyql> show slave status\G *************************** 1. row *************************** Connect_Retry: 60 Master_Log_FIle: mysql-bin.000002 Read_Master_Log_Pos: 106 Slave_IO_Running: Yes Slave_SQL_Running: Yes Slave_IO_Running 如果是 Yes 的话代表成功从主服务器中同步到 bin-log 日志 Slave_SQL_Running 如果是 Yes 的话代表成功执行 bin-log 日志中的 SQL 语句 此时的 Master_Log_FIle 和 Read_Master_Log_Pos 的值应该对应主服务器中的 show master status 命令的值 Connect_Retry 中的 60 代表每 60 秒就去主服务器同步 bin-log 日志 OK, 如果你看到的是那两个关键的 Yes， 那你就可以去测试了， 在主服务器插入新的数据， 再去从服务器查看， 不出意外的话， 你会兴奋一下， 数据已经同步了 这里再说一下其他经常用到的命令: 复制代码 代码如下: #启动复制线程 msyql> start slave #停止复制线程 msyql> stop slave #动态改变到主服务器的配置 msyql> change master to #查看从数据库运行进程 msyql> show processlist 这里也同时说一下操作中的常见错误: 问题: 从数据库无法同步 Slave_SQL_Running 值为 NO， 或 Seconds_Bebind_Master 值为 Null 原因: 解决方法一: 复制代码 代码如下: msyql> stop slave; msyql> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1; msyql> start slave; 解决方法二: 复制代码 代码如下: msyql> stop slave; #查看主服务器上当前的 bin-log 日志名和偏移量 msyql> show master status; #获取到如下内容: +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000005 | 286 | | | +------------------+----------+--------------+------------------+ #然后到从服务器上执行手动同步 msyql> change master to -> master_host="192.168.10.1" -> master_user="user1" -> master_password="123456" -> master_post=3306 -> master_log_file="mysql-bin.000005" -> master_log_pos=286; msyql> start slave; 再次通过 show slave status 查看: 如果 Slave_SQL_Running 的值变为 Yes， Seconds_Bebind_Master 的值为 0 时， 即正常 好了， 如上是我自己在操作中所总结的一些内容， 如有更好的建议， 欢迎留言一起探讨 顺便说一下， 我使用的是 Ubuntu 12.04 |
