设为首页 收藏本站
查看: 885|回复: 0

[经验分享] Mysql主从同步(1)

[复制链接]

尚未签到

发表于 2017-12-12 10:31:26 | 显示全部楼层 |阅读模式
为了测试效果,先在master机器上创建测试库  
mysql> CREATE DATABASE huanqiu CHARACTER SET utf8 COLLATE utf8_general_ci;
  
Query OK, 1 row affected (0.00 sec)
  
mysql> use huanqiu;
  
Database changed
  
mysql> create table if not exists haha (id int(10) PRIMARY KEY AUTO_INCREMENT,name varchar(50) NOT NULL);
  
Query OK, 0 rows affected (0.02 sec)
  
mysql> insert into huanqiu.haha values(1,"wangshibo"),(2,"guohuihui");
  
Query OK, 2 rows affected (0.00 sec)
  
Records: 2  Duplicates: 0  Warnings: 0
  
mysql> select * from huanqiu.haha;
  
+----+-----------+

  
|>  
+----+-----------+
  
|  1 | wangshibo |
  
|  2 | guohuihui |
  
+----+-----------+
  
2 rows in set (0.00 sec)
  
--------------------------------------------------------------------
  
温馨提示:
  
修改库或表的字符集

  
mysql>>
  
mysql>>  
添加主键

  
mysql>>
  
mysql>>  
删除主键时要先删除自增长,再删除主键

  
mysql>>
  
mysql>>  
--------------------------------------------------------------------
  
下面是master数据库上的操作:
  
1)设置master数据库的my.cnf文件(在[mysqld]配置区域添加下面内容)
  
[iyunv@master ~]# vim /usr/local/mysql/my.cnf
  
.......
  
server-id=1        #数据库唯一ID,主从的标识号绝对不能重复。
  
log-bin=mysql-bin    #开启bin-log,并指定文件目录和文件名前缀
  
binlog-do-db=huanqiu  #需要同步的数据库。如果是多个同步库,就以此格式另写几行即可。如果不指明对某个具体库同步,就去掉此行,表示同步所有库(除了ignore忽略的库)。
  
binlog-ignore-db=mysql  #不同步mysql系统数据库。如果是多个不同步库,就以此格式另写几行;也可以在一行,中间逗号隔开。
  
sync_binlog = 1      #确保binlog日志写入后与硬盘同步
  
binlog_checksum = none  #跳过现有的采用checksum的事件,mysql5.6.5以后的版本中binlog_checksum=crc32,而低版本都是binlog_checksum=none
  
binlog_format = mixed   #bin-log日志文件格式,设置为MIXED可以防止主键重复。
  
--------------------------------------------------------------------------------
  
温馨提示:在主服务器上最重要的二进制日志设置是sync_binlog,这使得mysql在每次提交事务的时候把二进制日志的内容同步到磁盘上,即使服务器崩溃也会把事件写入日志中。
  
sync_binlog这个参数是对于MySQL系统来说是至关重要的,他不仅影响到Binlog对MySQL所带来的性能损耗,而且还影响到MySQL中数据的完整性。对于"sync_binlog"参数的各种设置的说明如下:
  
sync_binlog=0,当事务提交之后,MySQL不做fsync之类的磁盘同步指令刷新binlog_cache中的信息到磁盘,而让Filesystem自行决定什么时候来做同步,或者cache满了之后才同步到磁盘。
  
sync_binlog=n,当每进行n次事务提交之后,MySQL将进行一次fsync之类的磁盘同步指令来将binlog_cache中的数据强制写入磁盘。
  
在MySQL中系统默认的设置是sync_binlog=0,也就是不做任何强制性的磁盘刷新指令,这时候的性能是最好的,但是风险也是最大的。因为一旦系统Crash,在binlog_cache中的所有binlog信息都会被丢失。而当设置为“1”的时候,是最安全但是性能损耗最大的设置。因为当设置为1的时候,即使系统Crash,也最多丢失binlog_cache中未完成的一个事务,对实际数据没有任何实质性影响。
  
从以往经验和相关测试来看,对于高并发事务的系统来说,“sync_binlog”设置为0和设置为1的系统写入性能差距可能高达5倍甚至更多。
  
--------------------------------------------------------------------------------
  
2)导出master数据库多余slave数据库中的数据,然后导入到slave数据库中。保证双方在同步环境实现前的数据一致。
  导出数据库之前先锁定数据库
  mysql> flush tables with read lock;    #数据库只读锁定命令,防止导出数据库的时候有数据写入。unlock tables命令解除锁定
  导出master数据库中多余的huanqiu库(master数据库的root用户登陆密码:123456)
[iyunv@master ~]# mysqldump -uroot huanqiu -p123456 >/opt/huanqiu.sql

[iyunv@master ~]# rsync  -e "ssh -p22" -avpgolr /opt/huanqiu.sql 182.148.15.237:/opt/   #将导出的sql文件上传到slave机器上

  
3)设置数据同步权限

  mysql> grant replication slave,replication client on *.* to slave@'182.148.15.237'>  Query OK, 0 rows affected (0.02 sec)
  mysql> flush privileges;
  Query OK, 0 rows affected (0.00 sec)
  

  
--------------------------------------------------------------------------------
  
温馨提示:
  
权限查看方式
  
mysql> show grants;
  
mysql> show grants for slave@'182.148.1115.237';
  
--------------------------------------------------------------------------------
  

  
4)查看主服务器master状态(注意File与Position项,从服务器需要这两项参数)
  
mysql> show master status;
  
+------------------+----------+--------------+------------------+-------------------+
  
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
  
+------------------+----------+--------------+------------------+-------------------+
  
| mysql-bin.000007 |      120 | huanqiu      | mysql            |                   |
  
+------------------+----------+--------------+------------------+-------------------+
  
1 row in set (0.00 sec)
  
下面是slave数据库上的操作:
  
1)设置slave数据库的my.cnf配置文件
  
[iyunv@master ~]# vim /usr/local/mysql/my.cnf
  
.......
  
server-id=2   #设置从服务器id,必须于主服务器不同
  
log-bin=mysql-bin   #启动MySQ二进制日志系统
  
replicate-do-db=huanqiu  #需要同步的数据库名。如果不指明同步哪些库,就去掉这行,表示所有库的同步(除了ignore忽略的库)。
  
replicate-ignore-db=mysql  #不同步mysql系统数据库
  
slave-skip-errors = all   #跳过所有的错误错误,继续执行复制操作
  
-----------------------------------------------------------------------------------------------
  
温馨提示:
  
当只针对某些库的某张表进行同步时,如下,只同步huanqiu库的haha表和huanpc库的heihei表:
  
replicate-do-db = huanqiu
  
replicate-wild-do-table = huanqiu.haha       //当只同步几个或少数表时,可以这样设置。注意这要跟上面的库指定配合使用;
  
replicate-do-db = huanpc
  
replicate-wild-do-table = huanpc.heihei      //如果同步的库的表比较多时,就不能这样一一指定了,就把这个选项配置去掉,直接根据指定的库进行同步。
  
-----------------------------------------------------------------------------------------------
  
2)在slave数据库中导入从master传过来的数据。
  
mysql> CREATE DATABASE huanqiu CHARACTER SET utf8 COLLATE utf8_general_ci;   #先创建一个huanqiu空库,否则下面导入数据时会报错说此库不存在。
  
mysql> use huanqiu;
  
mysql> source /opt/huanqiu.sql;   #导入master中多余的数据。
  
.......
  
3)配置主从同步指令
  
mysql> stop slave;   #执行同步前,要先关闭slave
  
mysql> change  master to master_host='182.148.15.238',master_user='slave',master_password='slave@123',master_log_file='mysql-bin.000007',master_log_pos=120;
  
mysql> start slave;
  
mysql> show slave status \G;
  
.......
  
*************************** 1. row ***************************
  Slave_IO_State: Waiting for master to send event
  Master_Host: 182.148.15.238
  Master_User: slave
  Master_Port: 3306
  Connect_Retry: 60
  Master_Log_File: mysql-bin.000007
  Read_Master_Log_Pos: 120
  Relay_Log_File: mysql-relay-bin.000002
  Relay_Log_Pos: 279
  Relay_Master_Log_File: mysql-bin.000007
  Slave_IO_Running: Yes
  Slave_SQL_Running: Yes
  Replicate_Do_DB: huanqiu
  Replicate_Ignore_DB: mysql
  .............
  Seconds_Behind_Master: 0
  
如上,当IO和SQL线程的状态均为Yes,则表示主从已实现同步了!
  
查看slave数据库中的数据情况
  
mysql> show databases;
  
+--------------------+
  
| Database           |
  
+--------------------+
  
| information_schema |
  
| huanqiu            |
  
| mysql              |
  
| performance_schema |
  
| test               |
  
+--------------------+
  
5 rows in set (0.00 sec)
  
mysql> select * from huanqiu.haha;
  
+----+-----------+

  
|>  
+----+-----------+
  
|  1 | wangshibo |
  
|  2 | guohuihui |
  
+----+-----------+
  
2 rows in set (0.00 sec)
  
下面测试下Mysql主从同步的效果
  
现在主数据库上写入新数据
  
mysql> unlock tables;    #解锁,否则新数据无法写入
  
mysql> insert into huanqiu.haha values(100,"anhui");
  
Query OK, 1 row affected (0.00 sec)
  
然后在slave数据库上查看,发现master上新写入的数据已经同步过来了
  
mysql> select * from huanqiu.haha;
  
+-----+-----------+

  
|>  
+-----+-----------+
  
|   1 | wangshibo |
  
|   2 | guohuihui |
  
| 100 | anhui     |
  
+-----+-----------+
  
3 rows in set (0.00 sec)
  
至此,主从同步环境已经实现!

运维网声明 1、欢迎大家加入本站运维交流群:群②:261659950 群⑤:202807635 群⑦870801961 群⑧679858003
2、本站所有主题由该帖子作者发表,该帖子作者与运维网享有帖子相关版权
3、所有作品的著作权均归原作者享有,请您和我们一样尊重他人的著作权等合法权益。如果您对作品感到满意,请购买正版
4、禁止制作、复制、发布和传播具有反动、淫秽、色情、暴力、凶杀等内容的信息,一经发现立即删除。若您因此触犯法律,一切后果自负,我们对此不承担任何责任
5、所有资源均系网友上传或者通过网络收集,我们仅提供一个展示、介绍、观摩学习的平台,我们不对其内容的准确性、可靠性、正当性、安全性、合法性等负责,亦不承担任何法律责任
6、所有作品仅供您个人学习、研究或欣赏,不得用于商业或者其他用途,否则,一切后果均由您自己承担,我们对此不承担任何法律责任
7、如涉及侵犯版权等问题,请您及时通知我们,我们将立即采取措施予以解决
8、联系人Email:admin@iyunv.com 网址:www.yunweiku.com

所有资源均系网友上传或者通过网络收集,我们仅提供一个展示、介绍、观摩学习的平台,我们不对其承担任何法律责任,如涉及侵犯版权等问题,请您及时通知我们,我们将立即处理,联系人Email:kefu@iyunv.com,QQ:1061981298 本贴地址:https://www.yunweiku.com/thread-423267-1-1.html 上篇帖子: MySQL版本升级之5.6到5.7 下篇帖子: Spark JDBC To MySQL
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

扫码加入运维网微信交流群X

扫码加入运维网微信交流群

扫描二维码加入运维网微信交流群,最新一手资源尽在官方微信交流群!快快加入我们吧...

扫描微信二维码查看详情

客服E-mail:kefu@iyunv.com 客服QQ:1061981298


QQ群⑦:运维网交流群⑦ QQ群⑧:运维网交流群⑧ k8s群:运维网kubernetes交流群


提醒:禁止发布任何违反国家法律、法规的言论与图片等内容;本站内容均来自个人观点与网络等信息,非本站认同之观点.


本站大部分资源是网友从网上搜集分享而来,其版权均归原作者及其网站所有,我们尊重他人的合法权益,如有内容侵犯您的合法权益,请及时与我们联系进行核实删除!



合作伙伴: 青云cloud

快速回复 返回顶部 返回列表