【澳门金沙vip】Centos7 Mysql 5.6 多主一从 解决方案与详细配置

业务场景:

Mysql目录

  • 数据库介绍、常见分类
  • Mysql入门
  • Mysql安装配置
  • Mysql多实例安装配置
  • Mysql常用基本命令
  • Mysql权限体系
  • Mysql数据库备份和恢复
  • Mysql日志
  • Mysql逻辑、物理备份和增量恢复
  • Mysql闪回技术之
    binlog2sql
  • Mysql字符集与中文乱码问题
  • Mysql主从主主级联复制
  • Mysql主库从库宕机灾难恢复原理实践
  • Mysqlbinlog日志三种模式
  • Mysql数据常用引擎
  • Mysql事务-隔离级别
  • Mysql半同步及读写分离
  • Mysql高可用方案
  • Mysql分库分表方案
  • mysql基于init-connect+binlog完成审计功能
  • Mysql数据库管理规范和管理思想
  • CentOS下使用MyTop实时监控MySQL
  • Mysql企业级参数以及SQL深度优化
  • Mysql企业常用集群图解
  • 高性能mysql集群演变过程(转)
  • Mysql
    cluster集群安装配置方案
  • Mysql常见错误类型
  • Mysql 面试题

 

MySQL 主从复制,mysql主从复制

 

公司几个主要的业务已经独立,放在不同的数据库服务器上面,但是有一个业务又需要关联多个业务库进行联合查询统计。这时候就需要将不同的业务库数据同步到一台从库进行统计。根据Mysql主从同步原理使用多从一主的方案解决。主库使用innodb引擎,从库开启多实例使用myisam引擎并将多个实例的数据同步到同一个目录,并通过flush
tables 在一个实例里面访问其他实例的数据。

Mysql中间件Mycat目录

  • Mycat前世今生
  • Mycat安装

 

 

mysql部署

  • 经典安装(二进制、rpm、源码)
  • 单机多实例
  • docker部署
  • my.conf配置文件详解
  • 目录、日志结构
  • 常见启动问题

mysql数据库对象全景图

 

mysql数据库建模

 

mysql常用功能实践

  • mysql字符集介绍以及乱码问题处理
  • mysql数据类型
  • DML使用
  • DDL、DCL使用

mysql运维管理

  • 了解mysql的运行情况(慢日志查看)
  • mysql监控(如何精准监控主从延迟)
  • 升级MySQL
  • MySQL审计

 

mysql备份相关

  • mysql备份(冷备、热备、Mysqldump参数)
  • mysql闪回
  • binlog日志格式
  • 从全备中恢复单表

mysql复制

  • 主从复制
  • 半同步复制
  • 多源同步
  • 延迟复制
  • 复制常见问题

 

mysql原理实践

  • mysql体系架构
  • mysql的索引原理
  • 锁原理
  • mysql存储引擎
  • innodb原理
  • innodb事物

 

1 复制概述

     
Mysql内建的复制功能是构建大型,高性能应用程序的基础。将Mysql的数据分布到多个系统上去,这种分布的机制,是通过将Mysql的某一台主机的数据复制到其它主机(slaves)上,并重新执行一遍来实现的。复制过程中一个服务器充当主服务器,而一个或多个其它服务器充当从服务器。主服务器将更新写入二进制日志文件,并维护文件的一个索引以跟踪日志循环。这些日志可以记录发送到从服务器的更新。当一个从服务器连接主服务器时,它通知主服务器从服务器在日志中读取的最后一次成功更新的位置。从服务器接收从那时起发生的任何更新,然后封锁并等待主服务器通知新的更新。

请注意当你进行复制时,所有对复制中的表的更新必须在主服务器上进行。否则,你必须要小心,以避免用户对主服务器上的表进行的更新与对从服务器上的表所进行的更新之间的冲突。

解决思路:

1.1 mysql支持的复制类型:

  (1):基于语句的复制: 
在主服务器上执行的SQL语句,在从服务器上执行同样的语句。MySQL默认采用基于语句的复制,效率比较高。  
            一旦发现没法精确复制时,   会自动选着基于行的复制。    
  (2):基于行的复制:把改变的内容复制过去,而不是把命令在从服务器上执行一遍.
从mysql5.0开始支持
  (3):混合类型的复制:
默认采用基于语句的复制,一旦发现基于语句的无法精确的复制时,就会采用基于行的复制。

澳门金沙vip 1

1.首先在主库上执行操作

1.1 设置server-id值并开启binlog参数

 根据前文MySQL
的主从同步原理,我们知道要实现主从复制,关键因素就是开启binlog日志功能,所以,我们首先打开主库的binlog日志参数。

1.2实现主从复制的必要条件:

(1)在配置文件my.cnf中配置server-id不能一样。

(2)在主库的配置文件my.cnf中开启log-bin。

查询是否开启代码如下:

 

  1. [[email protected] ~]# egrep "log-bin|server-id"/data/{3306,3307}/my.cnf
  2. /data/3306/my.cnf:log-bin =/data/3306/mysql-bin ##显示已经开启
  3. /data/3306/my.cnf:server-id =1
  4. /data/3307/my.cnf:#log-bin =/data/3307/mysql-bin
  5. /data/3307/my.cnf:server-id =3
  6. [[email protected] ~]#

如果未开启修改主库的配置文件 执行vim
/data/3306/my.cnf,编辑多实例3306的my.cnf配置文件,两个参数按如下内容修改:
澳门金沙vip 2 提示:
1.上面两参数要放在my.cnf中的[mysqld]模块下,否则会出错。
澳门金沙vip 3
1.3.修改完配置文件后,重启数据库

  1. [[email protected] ~]#/data/3306/mysql stop
  2. StopingMySQL...
  3. [[email protected] ~]#/data/3306/mysql start

1.4.我们也可以在登录数据库中,查看修改的两处配置是否生效; 使用show
variables;查看mysql究竟配置了什么参数;

  1. mysql> show variables like "log_bin";
  2. +---------------+-------+
  3. |Variable_name|Value|
  4. +---------------+-------+
  5. | log_bin | ON |
  6. +---------------+-------+
  7. 1 row in set (0.00 sec)
  8. mysql> show variables like "server_id";
  9. +---------------+-------+
  10. |Variable_name|Value|
  11. +---------------+-------+
  12. | server_id |1|
  13. +---------------+-------+
  14. 1 row in set (0.00 sec)

2.1 建立用于主从复制的账号
 根据主从复制的原理,从库要想和主库同步,必须有一个可以连接主库的账号,并且这个账号的权限是主库上创建的,权限是允许主库的从库连接并同步数据。
 

 

1、主数据库使用Innodb引擎,并设置sql_mode为 NO_AUTO_CREATE_USER
2、从库开启多实例,将多个主库里面的数据通过主从复制同步到同一个数据目录。从库的每个实例对应一个主库。多个实例使用同一个数据目录。
3、从库使用Myisam引擎,关闭从库默认的innodb引擎,Myisam引擎可以访问同一个数据目录里面其他实例的表。
4、从库的每个实例需要执行flush tables
才能看到其他实例表的数据变化,可以设置crontab任务计划每分钟在第一个实例刷新表,以便程序连接的默认实例能看到表的实时变化。
5、设置主库和从库的sql_mode都为NO_AUTO_CREATE_USER,只有这样主库的innodb引擎的sql同步到从库的时候才能执行成功。

2.1、登录mysql3306 实例主数据库

  1. [[email protected] ~]# mysql -uroot -p123456 -S /data/3306/mysql.sock

2.2、建立用于从库复制的账号rep:

  1. mysql> grant replication slave on *.* to 'rep'@'192.168.1.%' identified by '123456';
  2. Query OK,0 rows affected (0.00 sec)

2.3、查看授权

  1. mysql> drop user 'root'@'mysql';##为安全考虑,将无用的授权删除
  2. Query OK,0 rows affected (0.02 sec)
  3. mysql> select user,host from mysql.user;
  4. +------+-------------+
  5. | user | host |
  6. +------+-------------+
  7. | root |127.0.0.1|
  8. | rep |192.168.1.%|
  9. | root | localhost |
  10. +------+-------------+
  11. 3 rows in set (0.00 sec)
  12. mysql> flush privileges; ##刷新权限 Query OK, 0 rows affected (0.00 sec)

2.4 查看我们做过授权的用户,所具备的权限
澳门金沙vip 4 2.5
给数据库一个读锁,是的用户无法创建库,只能读:

  1. mysql> flush table with read lock; ##将数据库表锁起来,锁表窗口不能退出,否则锁表失效
  2. Query OK,0 rows affected (0.00 sec)
  3. mysql> create database zhu;
  4. ERROR 1223(HY000):Can't execute the query because you have a conflicting read lock

2.6记录锁表的位置点
  1. mysql> show master status;##查看锁表的位置点
  2. +------------------+----------+--------------+------------------+
  3. |File|Position|Binlog_Do_DB|Binlog_Ignore_DB|
  4. +------------------+----------+--------------+------------------+
  5. | mysql-bin.000004|894|||
  6. +------------------+----------+--------------+------------------+
  7. 1 row in set (0.00 sec)
  8. mysql>

  2.7 锁表打包数据库

  1. [[email protected] ~]# mysqldump -uroot -p123456 --events -S /data/3306/mysql.sock -A -B|gzip >/opt/bak_$(date +%F).sql.gz
  2. [[email protected] ~]# ll /opt/
  3. 总用量148
  4. -rw-r--r--.1 root root 1443244月900:13 bak_2016-04-09.sql.gz

2.8
锁表打包完毕后,检查位置点有无变化,如果有变化,表示在打包的过程中有数据写入,锁表无效

  1. mysql> show master status;##锁表后位置点事894
  2. +------------------+----------+--------------+------------------+
  3. |File|Position|Binlog_Do_DB|Binlog_Ignore_DB|
  4. +------------------+----------+--------------+------------------+
  5. | mysql-bin.000004|894|||
  6. +------------------+----------+--------------+------------------+
  7. 1 row in set (0.00 sec)
  8. mysql> show master status;##锁表打包后,位置点没有变化,表示打包数据完成
  9. +------------------+----------+--------------+------------------+
  10. |File|Position|Binlog_Do_DB|Binlog_Ignore_DB|
  11. +------------------+----------+--------------+------------------+
  12. | mysql-bin.000004|894|||
  13. +------------------+----------+--------------+------------------+
  14. 1 row in set (0.00 sec)

2.9 接着将锁表打开

  1. mysql> unlock tables;
  2. Query OK,0 rows affected (0.00 sec)

  1. mysql> create database zhu;##解锁以后可以写数据
  2. Query OK,1 row affected (0.03 sec)
  3. mysql>

  2.9.1 查看生成的binlog文件
澳门金沙vip 5 2.9.2
使用mysqlbinlog命令查看生成的binlog日志,mysqlbinlog
专门是将二进制语句翻译成sql语句的工具:

  1. [[email protected] 3306]# mysqlbinlog mysql-bin.000004

3.接着做恢复从库的工作

  1. [[email protected] opt]# cd /opt/
  2. [[email protected] opt]# ll
  3. 总用量664
  4. -rw-r--r--.1 root root 5282954月900:50 all-tmp.sql
  5. -rw-r--r--.1 root root 1443244月900:13 bak_2016-04-09.sql.gz
  6. drwxr-xr-x.2 root root 40963月262015 rh
  7. [[email protected] opt]# gzip -d bak_2016-04-09.sql.gz
  8. [[email protected] opt]# mysql -uroot -p123456 -S /data/3307/mysql.sock <bak_2016-04-09.sql
  9. [[email protected] opt]#

  3.1.1 接着在从库上执行change master
to命令,实质实在从库上生成master.info文件的过程

  1. [[email protected] opt]# mysql -uroot -p123456 -S /data/3307/mysql.sock<<EOF
  2. stop slave;
  3. CHANGE MASTER TO
  4. MASTER_HOST='192.168.1.31',
  5. MASTER_PORT=3306,
  6. MASTER_USER='rep',
  7. MASTER_PASSWORD='123456',
  8. MASTER_LOG_FILE='mysql-bin.000004',
  9. MASTER_LOG_POS=894;
  10. EOF

澳门金沙vip 6   3.1.2
最后一步开启从库开关:

  1. mysql> start slave; ##打开开关
  2. Query OK,0 rows affected (0.00 sec)
  3. mysql>

接着检查从库状态信息:

  1. mysql> show slave status \G;
  2. ***************************1. row ***************************
  3. Slave_IO_State:Waitingfor master to send event
  4. Master_Host:192.168.1.31
  5. Master_User: rep
  6. Master_Port:3306
  7. Connect_Retry:60
  8. Master_Log_File: mysql-bin.000004
  9. Read_Master_Log_Pos:975
  10. Relay_Log_File: relay-bin.000002
  11. Relay_Log_Pos:334
  12. Relay_Master_Log_File: mysql-bin.000004
  13. Slave_IO_Running:Yes
  14. Slave_SQL_Running:Yes
  15. Replicate_Do_DB:
  16. Replicate_Ignore_DB: mysql
  17. Replicate_Do_Table:
  18. Replicate_Ignore_Table:
  19. Replicate_Wild_Do_Table:
  20. Replicate_Wild_Ignore_Table:
  21. Last_Errno:0
  22. Last_Error:
  23. Skip_Counter:0
  24. Exec_Master_Log_Pos:975
  25. Relay_Log_Space:484
  26. Until_Condition:None
  27. Until_Log_File:
  28. Until_Log_Pos:0
  29. Master_SSL_Allowed:No
  30. Master_SSL_CA_File:
  31. Master_SSL_CA_Path:
  32. Master_SSL_Cert:
  33. Master_SSL_Cipher:
  34. Master_SSL_Key:
  35. Seconds_Behind_Master:0
  36. Master_SSL_Verify_Server_Cert:No
  37. Last_IO_Errno:0
  38. Last_IO_Error:
  39. Last_SQL_Errno:0
  40. Last_SQL_Error:
  41. Replicate_Ignore_Server_Ids:
  42. Master_Server_Id:1
  43. 1 row in set (0.00 sec)

3.1.3 最后测试主从   主库创建库:
澳门金沙vip 7 从库:
澳门金沙vip 8  
最佳方案: 4.1 无须锁表的mysqldump备份命令       myisam
引擎企业生产备份命令: mysqldump -uroot -p123456 -A -B –mastet-data=2
-x –events|gzip >/opt /all.sql.gz       innodb
引擎企业生产备份命令:推荐使用的 mysqldump -uroot -p123456    -A     -B
    -F     –mastet-data=2    –events   –single-transaction|gzip  
>/opt/all.sql.gz  

  1. [[email protected] opt]# mysqldump -uroot -p123456 -S /data/3306/mysql.sock -A -B --master-data=2-x --events >/opt/all-tmp.sql ##演示如下命令
  2. [[email protected] opt]# vim all-tmp.sql
  3. 其中–master-data=2,表示在前面加–

  4. -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=975;##记录了增量备份的起点位置,主要是参数--master-data=2起的作用

  注: –master-data作用: 1、使用–master-data=2
进行备份文件会增加如下内容:适合普通备份增量恢复  

主从复制,mysql主从复制 1 复制概述
Mysql内建的复制功能是构建大型,高性能应用程序的基础。将Mysql的数据分布到多个系统上去,这种…

方案架构图:

澳门金沙vip 9

环境说明:

主库-1:192.168.1.1
主库-2:192.168.1.2
从库-3:192.168.1.3
从库-3:192.168.1.4
从库-3:192.168.1.5

实现步骤:(Mysql安装步骤这里不在描述)

1、主数据库配置文件,多个主库配置文件除了server-id不能一样其他都一样。