MYSQL教程Mysql主从复制(master-slave)实际操作案例
《MYSQL教程Mysql主从复制(master-slave)实际操作案例》要点: 在这一章节里,我们来了解下如安在 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 配置文件: ? /********** 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.2G ? *************************** 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 statusG ? *************************** 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 编程之家PHP培训学院每天发布《MYSQL教程Mysql主从复制(master-slave)实际操作案例》等实战技能,PHP、MYSQL、LINUX、APP、JS,CSS全面培养人才。 (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |