MySQL主备双活安装说明

MySQL主备双活安装阐明一、 MySQL主备工作原理MySQL主备集群模式采用日记回放的模式，将主服务器上执行过的日记信息，在备机上重新进行一次回放，保证主备服务器数据的一致性。简朴而言就是把一种服务器上执行过的sql语句在别的服务器上也反复执行一遍，只要两个数据库的初态是同样的，那么它们就能始终同步。对于一种mysql服务器，一般有两个线程来负责复制和被复制。1. 作为主服务器Master，会把自己的每一次改动都记录到 二进制日记 Binarylog 中；2. 作为从服务器Slave，会用master上的账号登陆到 master上， 读取master的Binarylog，写入到自己的中继日记 Relaylog，然后自己的sql线程会负责读取这个中继日记，并执行一遍。MySQL主备双活即主服务器负责写入读取，从服务器则只能进行读取操作。MySQL主主双活则在主备的基本上，采用互为备份的方式。二、 MySQL双活配备2.1. 环境阐明两台服务器，IP地址分别为10.68.19.182(如下简称A机)、10.68.19.183（如下简称B机），操作系统Ubuntu 14.04。2.2. MySQL安装在两台服务器上分别安装MySQL服务器。$ sudo apt-get install mysql-server2.3. 创立备份顾客在A机上执行mysql> grant replication slave on *.* to 'repl''10.68.19.183' identified by 'replpwd'mysql> flush privileges;在B机上执行mysql> grant replication slave on *.* to 'repl''10.68.19.182' identified by 'replpwd'mysql> flush privileges;2.4. 修改MySQL配备在A机上修改配备文献$ sudo vi /etc/mysql/f#bind-address = 127.0.0.1server-id = 101log_bin = /var/log/mysql/mysql-bin.logexpire_logs_days = 10max_binlog_size = 100M#binlog_do_db = include_database_namebinlog_ignore_db = mysqlbinlog_ignore_db = information_schemabinlog_ignore_db = performance_schemalog-slave-updatessync_binlog=0auto-increment-increment= 2auto-increment-offset = 1备注阐明：1、 bind-address注释掉或者修改为本地IP地址，否则外部机器无法连接到MySQL服务器；2、 server-id为服务器的ID值，两台不同的MySQL服务器，必须配备成不同；3、 binlog_do_db为需要同步的数据库，目前部署采用采用排除模式，不进行设立；4、 binlog_ignore_db设定忽视哪些数据库的日记，目前的配备排除了系统自带的数据库；5、 log-slave-updates这个参数用来配备从服务器的更新与否写入二进制日记，例如A->B B->A，在 B中设立log_slave_updates后还可以B->C. 这样A,C中的数据也是一致的，在主主互备的方式中，日记会自动过滤自己发送给其他服务器的日记；6、 sync_binlog相应的数值为0或任意整形数据，如果不小于0，当每个sync_binlog写入该二进制日记后，MySQL服务器将它的二进制日记同步到硬盘上(fdatasync()。请注意如果在autocommit模式，每执行一种语句向二进制日记写入一次，否则每个事务写入一次。 默认值是0，不与硬盘同步。值为1是最安全的选择，由于崩溃时，你最多丢掉二进制日记中的一种语句/事务；7、 auto-increment-increment、auto-increment-offset用于控制自增长主键生成方略，避免不同服务器之间产生的id发生冲突。auto-increment-increment用于设定自动增长字段的数值间隔，auto-increment-offset用于设定自动增长的偏移量（每台MySQL需配备不同值）。在本应用设立中，A机自动增长的数值为1、3、5，B机自动增长的数值为2、4、6。在B机上修改配备文献$ sudo vi /etc/mysql/f#bind-address = 127.0.0.1server-id = 102log_bin = /var/log/mysql/mysql-bin.logexpire_logs_days = 10max_binlog_size = 100M#binlog_do_db = include_database_namebinlog_ignore_db = mysqlbinlog_ignore_db = information_schemabinlog_ignore_db = performance_schemalog-slave-updatessync_binlog=0auto-increment-increment = 2auto-increment-offset = 2分别在两台服务器上重启MySQL$ sudo /etc/init.d/mysql restart或者$ sudo service mysql restart备注：注意启动提示，如未正常提示数据库启动成功，请查看服务器/var/log/mysql目录中的日记内容。2.5. 同步数据库在A机上执行-创立数据库create database testdb default charset utf8;-切换数据库use testdb;-创立测试表create table DM_TEST( ID INT AUTO_INCREMENT PRIMARY KEY, NAME VARCHAR(100);-插入测试数据insert DM_TEST(NAME) values('user1');insert DM_TEST(NAME) values('user2');-将表设定为只读FLUSH TABLES WITH READ LOCK;-显示表日记状况show master statusG-备份数据库mysqldump -master-data -uroot -p testdb > testdb.sqlscp testdb.sql 10.68.19.183:/home/whcyit在B机上执行-创立数据库create database testdb default charset utf8;-恢复数据库mysql -uroot -p testdb < /home/whcyit/testdb.sql-设定初始日记change master to master_host='10.68.19.182',master_user='repl',master_password='replpwd',master_log_file='mysql-bin.000001',master_log_pos=94382;-启动同步start slave;-锁定表FLUSH TABLES WITH READ LOCK;-显示表日记状况show master statusG在A机上执行-解除只读unlock tables;-锁定表change master to master_host='10.68.19.183',master_user='repl',master_password='replpwd',master_log_file='mysql-bin.000001',master_log_pos=107;-启动同步start slave;在B机上执行-解除只读unlock tables;分别在两台机器上执行如下命令查看从服务器状态mysql> show slave statusG;确认Slave_IO_Running、Slave_SQL_Running为Yes状态。2.6. 验证分别在两个数据库，分别插入数据，看另一数据库与否正常显示数据。停止一种数据库，操作另一数据库后，再次启动数据库，查看数据与否同步完毕。三、 MySQL常用配备项阐明使用MySQL的命令show variables like 'max_connections' 、show status like 'max_connections'可以查询配备内容，修改配备需直接修改f文献。参照配备：innodb_buffer_pool_size=4Ginnodb_log_file_size=1024Minnodb_log_buffer_size = 8Minnodb_flush_log_at_trx_commit=2innodb_file_per_table=1innodb_file_io_threads=4innodb_flush_method=O_DIRECTinnodb_io_capacity=innodb_thread_concurrency = 0innodb_additional_mem_pool_size=16Minnodb_autoinc_lock_mode = 23.1. innodb_buffer_pool_size缓冲池是数据和索引缓存的地方：这个值越大越好，这能保证你在大多数的读取操作时使用的是内存而不是硬盘。典型的值是5-6GB(8GB内存)，20-25GB(32GB内存)，100-120GB(128GB内存)。3.2. innodb_log_file_sizeRedo日记的大小设立，redo日记被用于保证写操作迅速而可靠并且在崩溃时恢复。设立较大的值，可以提高数据库性能（避免日记频繁切换），设立较小的值，可以使得来MySQL崩溃后可以更快恢复。可以考虑将把innodb_log_file_size设立成512M(默认2个日记文献，这样有1GB的redo日记)会使你有富余的写操作空间。如果应用程序需要频繁的写入数据并且你使用的时MySQL 5.6，你可以一开始就设立为4G。3.3. max_connections最大顾客连接数，默认值为151。设立过大的值（例如1000或更高）后，服务器运营1000个或更高的活动事务时会变的没有响应。3.4. innodb_thread_concurrency并发线程数，推荐设立为 2*(NumCPUs+NumDisks)，默认一般为8。3.5. innodb_file_per_table设立InnoDB与否需要将所有表的数据和索引寄存在共享表空间里（innodb_file_per_table = OFF） 或者为每张表的数据单独放在一种.ibd文献（innodb_file_per_table = ON）。每张表一种文献容许你在drop、truncate或者rebuild表时回收磁盘空间。这对于某些高档特性也是有必要的，例如数据压缩。但是它不会带来任何性能收益。你不想让每张表一种文献的重要场景是：有非常多的表（例如10k+）。MySQL 5.6中，这个属性默认值是ON，因此大部分状况下你什么都不需要做。对于之前的版本你必需在加载数据之前将这个属性设立为ON，由于它只对新创立的表有影响。3.6. innodb_log_buffer_size该配备决定了为尚未执行的事务分派的缓存。其默认值（1MB）一般来说已经够用了，但是如果你的事务中包具有二进制对象或者大文本字段的话，这点缓存不久就会被填满并触发额外的I/O操作。3.7. query_cache_sizeQuery cache（查询缓存）是一种众所周知的瓶颈，甚至在并发并不多的时候也是如此。 最佳选项是将其从一开始就停用，设立query_cache_size = 0（目前MySQL 5.6的默认值）并运用其她措施加速查询：优化索引、增长拷贝分散负载或者启用额外的缓存（例如memcache或redis）。3.8. log_bin日记文献寄存途径，对于主备双活、主主双活的方案，主机上必须启动日记文献，单纯的从服务器可以不启动日记服务。在设立log_bin的同步，需server_id作为服务器的唯一标记。在对于基于时间点的数据恢复的场景，日记文献也必须启动，即采用最新的全备数据恢复，然后用日记文献进行前滚。二进制日记一旦创立就将永久保存。因此如果你不想让磁盘空间耗尽，你可以用 PURGE BINARY LOGS 来清除旧文献，或者设立 expire_logs_days 来指定过多少天日记将被自动清除。记录二进制日记不是没有开销的，因此如果你在一种非主节点的复制节点上不需要它的话，那么建议关闭这个选项。3.9. skip_name_resolve当客户端连接数据库服务器时，服务器会进行主机名解析，并且当DNS很慢时，建立连接也会很慢。因此建议在启动服务器时关闭skip_name_resolve选项而不进行DNS查找。唯一的局限是之后GRANT语句中只能使用IP地址。