MySQL主备双活安装说明

1、MySQL主备双活安装说明 一、MySQL主备工作原理 MySQL主备集群模式采用日志回放的模式， 将主服务器上执行过的日志信息，在备机上重新进行一次回放，确保主备服务器数据的一致性。简单而言就是把一个服务器上执行过的sql语句在别的服务器上也重复执行一遍，只要两个数据库的初态是一样的，那么它们就能一直同步。 MasterSlave 对于一个mysql服务器，一般有两个线程来负责复制和被复制。 1 .作为主服务器Master,会把自己的每一次改动都记录到二进制日 志Binarylog中； 2 .作为从服务器Slave,会用master上的账号登陆到master上，读取master的Binary

2、log,写入到自己的中继日志Relaylog然后自己的sql线程会负责读取这个中继日志，并执行一遍。 MySQL主备双活即主服务器负责写入读取， 从服务器则只能进行读取操作。MySQL主主双活则在主备的基础上， 采用互为备份的方式。 二、MySQL双活配置 2.1. 环境说明 两台服务器，IP地址分别为10.68.19.182（以下简称A机）、 10.68.19.183（以下简称B机），操作系统Ubuntu14.04。 2.2. MySQ比装 在两台服务器上分别安装MySQL服务器。 $sudoapt-getinstallmysql-server 2.3. 创建备份用户 在A机上执行 mysq

3、lgrant replicationslaveon*.*torepl10.68.19.183identified byreplpwd; mysqlflushprivileges; 在B机上执行 mysqlgrant replicationslaveon*.*torepl10.68.19.182identified byreplpwd; mysqlflushprivileges; 2.4. 修改MySQL配置 在A机上修改配置文件 $sudovi/etc/mysql/f #bind-address =127.0.0.1 server-id =101 log_binexpire_logs_days

4、max_binlog_size#binlog_do_dbbinlog_ignore_dbbinlog_ignore_db =/var/log/mysql/mysql-bin.log =10 =100M =include_database_name =mysql =information_schema binlog_ignore_db=performance_schema log-slave-updates sync_binlog=0 auto-increment-increment=2 auto-increment-offset=1 备注说明: 1、bind-address注释掉或者修改为本地

5、IP地址，否则外部机器无法连接到MySQL服务器； 2、server-id为服务器的ID值，两台不同的MySQL服务器，必须配置成不同； 3、binlog_do_db为需要同步的数据库，当前部署采用采用排除模式，不进行设置； 4、binlog_ignore_db设定忽略哪些数据库的日志，当前的配置排除了系统自带的数据库； 5、log-slave-updates这个参数用来配置从服务器的更新是否写入二进制日志，例如 A-BB-A在B中设置log_slave_updates后还可以B-C.这样A,C中的数据也是一致的，在主主互备的方式中，日志会自动过滤自己发送给其它服务器的日志； 6、sync_b

6、inlog对应的数值为0或任意整形数据，如果大于0,当每个sync_binlog写入该二进制日志后,MySQL服务器将它的二进制日志同步到硬盘上（fdatasync （） ）。 请注意如果在autocommit模式，每执行一个语句向二进制日志写入一次，否则每个事务写入一次。默认值是0,不与硬盘同步。值为1是最安全的选择，因为崩溃时，你最多丢掉二进制日志中的一个语句/事务； 7、auto-increment-increment、auto-increment-offset用于控制自增长主键生成策略，避免不同服务器之间产生的id发生冲突。auto-increment-increment用于设定自动增

7、长字段的数值间隔，auto-increment-offset用于设定自动增长的偏移量（每台MySQL需配置不同值）。在本应用设置中，A机自动增长的数值为1、3、5，B机自动增长的数值为2、4、6。 在B机上修改配置文件 $sudovi/etc/mysql/f =127.0.0.1 =102 =/var/log/mysql/mysql-bin.log =10#bind-address server-id log_bin expire_logs_daysmax_binlog_size =100M #binlog_do_dbbinlog_ignore_dbbinlog_ignore_dbbinlog

8、_ignore_dblog-slave-updatessync_binlog=0auto-increment-increment=2auto-increment-offset=2 分别在两台服务器上重启MySQL $sudo/etc/init.d/mysqlrestart 或者 $sudoservicemysqlrestart 备注：注意启动提示，如未正常提示数据库启动成功，请查看服务器 /var/log/mysql目录中的日志容。 3.6. 同步数据库 在A机上执行 -创建数据库createdatabasetestdbdefaultcharsetutf8; -切换数据库usetestdb;

9、-创建测试表 createtableDM_TEST( IDINTAUTO_INCREMENTPRIMARYKEY,NAMEVARCHAR(100) ); -插入测试数据insertDM_TEST(NAME)values(user1); insertDM_TEST(NAME)values(user2); -将表设定为只读FLUSHTABLESWITHREADLOCK; -显示表日志情况showmasterstatusG mysqlmysqlshowmasterstatusshowmasterstatus G G； 李承率率零事洋空字小字手生宇丰宇丰牛字丰牛手邛室封封封1T|Ty军学牢本宰丰军军军

10、军军军事车本军手军军本本牢本事寺:本军 Filoin)ysql-bin-0C0001 Position：94382 Biniog_LJL_UB： BinlogwIgnorG_DB：nvsql,infcrmti匚一三二七曰皿且，perfnnance_schem& 1rowin与曰t(0.QQwu： ERROR： NoQueryspecified -备份数据库 mysqldump-master-data-uroot-ptestdbtestdb.sql =include_database_name =mysql =information_schema =performance_schema

11、scptestdb.sql10.68.19.183:/home/whcyit 在B机上执行 -创建数据库 createdatabasetestdbdefaultcharsetutf8; -恢复数据库 mysql-uroot-ptestdbshomasterstatusG; File二000001 Position：107 diuiuLDu_D山 Bin1oIgnore_DB:niysqlinfarmation_scheinaperforirance_schema1TOKinset(0.00sec) ERROR： Noqueryspecifid- 在A机上执行 -解除只读 unlocktable

12、s -锁定表 changemastertomaster_host=10.68.19.183,master_user=repl master_password=replpwd,master_log_file=mysql-bin.000001, master_log_pos=107; -启动同步startslave; 在B机上执行 -解除只读unlocktables; 分别在两台机器上执行以下命令查看从服务器状态 mysqlshowslavestatusG; ReLay_Lolie：mysLd-relay-bin-000004 Relay_Log_Pos：488 Relay_Master_Log_

13、Fileimysql-bin.000001 Slave_IO_Running；Yes S1s.ve_SQL_Runni门区：YesK-=C_lC=tQ_LJQ_lJEi1 Replict_Isnor_DB； Replicate_Do_Table： RQplicatQ_IgnorQ_TQblGi Replicate_U1d_Do_Table： 确认Slave_IO_RunningSlave_SQL_Runnin的Yes状态。 2.6.验证 分别在两个数据库，分别插入数据，看另一数据库是否正常显示数据。 停止一个数据库，操作另一数据库后，再次启动数据库，查看数 据是否同步完成。 三、MySQL常用

14、配置项说明 使用MySQL的命令showvariableslikemax_connections、showstatuslikemax_connections可以查询配置容，修改配置需直接修改f文件。 参考配置： innodb_buffer_pool_size=4G innodb_log_file_size=1024M innodb_log_buffer_size=8M innodb_flush_log_at_trx_commit=2 innodb_file_per_table=1 innodb_file_io_threads=4 innodb_flush_method=O_DIRECT inn

15、odb_io_capacity=2000 innodb_thread_concurrency=0 innodb_additional_mem_pool_size=16M innodb_autoinc_lock_mode=2 innodb_buffer_pool_size 缓冲池是数据和索引缓存的地方：这个值越大越好，这能保证你在大多数的读取操作时使用的是存而不是硬盘。 典型的值是5-6GB （8G昭） ，20-25GB（32G匿），100-120GB（128G防）。 innodb_log_file_size Redo日志的大小设置，redo日志被用于确保写操作快速而可靠并且在崩溃时恢复。设置较

16、大的值，可以提高数据库性能（避免日志频繁切换），设置较小的值，可以使得来MySQL崩溃后能够更快恢复。 可以考虑将把innodb_log_file_size设置成512M（默认2个日志文件，这样有1GB的redo日志）会使你有充裕的写操作空间。如果应用程序需要频繁的写入数据并且你使用的时MySQL5.6,你可以一开始 就设置为4G。 max_connections 最大用户连接数，默认值为151。设置过大的值（例如1000或更高）后，服务器运行1000个或更高的活动事务时会变的没有响应 innodb_thread_concurrency 并发线程数，推荐设置为2*（NumCPUs+NumDis

17、ks）默认一般为8。3.5.innodb_file_per_table 设置InnoDB是否需要将所有表的数据和索引存放在共享表空间里（innodb_file_per_table=OFF）或者为每表的数据单独放在一个.ibd文件（innodb_file_per_table=ON）。每表一个文件允许你在drop、truncate或者rebuild表时回收磁盘空间。这对于一些高级特性也是有必要的，比如数据压缩。但是它不会带来任何性能收益。你不想让每表一个文件的主要场景是：有非常多的表（比如10k+）。 MySQL5.6中，这个属性默认值是ON,因此大部分情况下你什么都不需要做。 对于之前的版本你必

18、需在加载数据之前将这个属性设置为ON,因为它只对新创建的表有影响。 innodb_log_buffer_size 该配置决定了为尚未执行的事务分配的缓存。其默认值（1MB）一般来说已经够用了，但是如果你的事务中包含有二进制对象或者大文本字段的话，这点缓存很快就会被填满并触发额外的I/O操作。 query_cache_size Querycache（查询缓存）是一个众所周知的瓶颈，甚至在并发并不多的时候也是如此。最佳选项是将其从一开始就停用，设置 query_cache_size=0（现在MySQL5.6的默认值）并利用其他方法加速查询：优化索引、增加拷贝分散负载或者启用额外的缓存（比如memcache或redis）。 log_bin 日志文件存放路径，对于主备双活、主主双活的方案，主机上必须开启日志文件，单纯的从服务器可以不开启日志服务。在设置log_bin的同时，需server_id作为服务器的唯一标识。 在对于基于时间点的数据恢复的场景，日志文件也必须开启，即采用最新的全备数据恢复，然后用日