MySQL 5.7和MariaDB 10提供了Multi-Master复制的功能，并在复制性能上有所改善。对比上一篇Master-Standy的配置，本文将测试一下Multi-Master的配置。 Multi-Master 复制通常是环形复制，可以在任意主机上将数据复制给其他主机。 1. 安装MariaDB mariaDB为Re
MySQL 5.7和MariaDB 10提供了Multi-Master复制的功能，并在复制性能上有所改善。对比上一篇Master-Standy的配置，本文将测试一下Multi-Master的配置。
Multi-Master 复制通常是环形复制，可以在任意主机上将数据复制给其他主机。
1. 安装MariaDB
mariaDB为Redhat/CentOS提供了yum源的方式安装。登下下面的链接就可以选择对应平台的repo配置：
https://downloads.mariadb.org/mariadb/repositories/#mirror=tsinghua
那么我这里环境是2台CentOS 6.5的虚拟机:
OStack01 192.168.1.132
OStack02 192.168.1.133
获得的repo信息如下：
# vim /etc/yum.repos.d/MariaDB.repo# MariaDB 10.0 CentOS repository list - created 2014-05-02 15:17 UTC# http://mariadb.org/mariadb/repositories/[mariadb]name = MariaDBbaseurl = http://yum.mariadb.org/10.0/centos6-amd64gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDBgpgcheck=1
配置好repo之后，使用yum安装即可。这里还需要安装MariaDB-client的包才有会mysql, mysqladmin等客户端工具。
# yum search MariaDBLoaded plugins: fastestmirror, refresh-packagekit, securityLoading mirror speeds from cached hostfile * base: centos.ustc.edu.cn * epel: ftp.sjtu.edu.cn * extras: centos.ustc.edu.cn * updates: centos.ustc.edu.cnepel/pkgtags | 1.0 MB 00:07 ============================= N/S Matched: MariaDB =============================MariaDB-Galera-server.x86_64 : MariaDB: a very fast and robust SQL database : serverMariaDB-cassandra-engine.x86_64 : MariaDB: a very fast and robust SQL database : serverMariaDB-client.x86_64 : MariaDB: a very fast and robust SQL database serverMariaDB-common.x86_64 : MariaDB: a very fast and robust SQL database serverMariaDB-compat.x86_64 : MariaDB: a very fast and robust SQL database serverMariaDB-connect-engine.x86_64 : MariaDB: a very fast and robust SQL database : serverMariaDB-devel.x86_64 : MariaDB: a very fast and robust SQL database serverMariaDB-oqgraph-engine.x86_64 : MariaDB: a very fast and robust SQL database : serverMariaDB-server.x86_64 : MariaDB: a very fast and robust SQL database serverMariaDB-shared.x86_64 : MariaDB: a very fast and robust SQL database serverMariaDB-test.x86_64 : MariaDB: a very fast and robust SQL database serverinnotop.noarch : A MySQL and InnoDB monitor program Name and summary matches only, use "search all" for everything.# yum install -y MariaDB-server MariaDB-client......Dependencies Resolved================================================================================ Package Arch Version Repository Size================================================================================Installing: MariaDB-compat x86_64 10.0.10-1.el6 mariadb 2.7 M replacing mysql-libs.x86_64 5.1.71-1.el6 MariaDB-server x86_64 10.0.10-1.el6 mariadb 52 MInstalling for dependencies: MariaDB-common x86_64 10.0.10-1.el6 mariadb 23 k perl-DBI x86_64 1.609-4.el6 base 705 kTransaction Summary================================================================================Install 4 Package(s)Total download size: 55 M...Installed: MariaDB-compat.x86_64 0:10.0.10-1.el6 MariaDB-server.x86_64 0:10.0.10-1.el6 Dependency Installed: MariaDB-common.x86_64 0:10.0.10-1.el6 perl-DBI.x86_64 0:1.609-4.el6
下面初始化并启动数据库。
# mysql_install_db --user=mysqlInstalling MariaDB/MySQL system tables in '/var/lib/mysql' .........# service mysql startStarting MySQL. SUCCESS! # mysql_secure_installationEnter current password for root (enter for none): OK, successfully used password, moving on...Set root password? [Y/n] YNew password: Re-enter new password: Password updated successfully!Reloading privilege tables.. ... Success!Remove anonymous users? [Y/n] Y ... Success!Disallow root login remotely? [Y/n] Y ... Success!Remove test database and access to it? [Y/n] n ... skipping.Reload privilege tables now? [Y/n] Y ... Success!Cleaning up...All done! If you've completed all of the above steps, your MariaDBinstallation should now be secure.Thanks for using MariaDB!
2. 配置参数文件
在参数文件/etc/my.cnf中，除了配置不同的server-id之外，下面的配置也是非常有用的。
vim /etc/my.cnf
log_bin = mysql-binthread_concurrency = 2
thread_concurrency视服务器的CPU数量设置，通常为CPU# * 2;innodb_file_per_table = 1
innodb_open_files=800
InnoDB 默认会将所有的数据库InnoDB引擎的表数据存储在一个共享空间中：ibdata1，这样就感觉不爽，增删数据库的时候，ibdata1文件不会自动收缩，单个数据库的备份也将成为问题，通常只能将数据使用mysqldump 导出，然后再导入解决这个问题。
当innodb数据量足够大时，共享表空间文件已经能够不合适。这时需要修改mysql配置文件的[mysqld] – innodb_file_per_table参数，可将InnoDB设为独立表空间模式，这样之后创建的每个数据库的每个表都会生成一个数据空间。
如果需要对现有表使用独享表空间，则需要alter table xxx engi本文来源gao@!dai!ma.com搞$$代^@码网*ne=innodb;让表重新生成一次。
使用独立表空间有下面优点：
1.每个表都有自已独立的表空间。
2.每个表的数据和索引都会存在自已的表空间中。
3.可以实现单表在不同的数据库中移动。
4.空间可以回收（drop/truncate table方式操作表空间不能自动回收）
5.对于使用独立表空间的表，在进行频繁delete处理后，表空间的碎片不会造成严重的性能损失，可以在后面单独优化。
缺点：
单表增加比共享空间方式更大。共享表在Insert操作时有一些优势。
查看参数show variables like ‘%per_table%’;
innodb默认打开的文件数量innodb_open_files是300，当启用了innodb_file_per_table后,这个值应该设置的足够大。
relay-log=relay-binlog 中继日志的存储位置及命名方式auto-increment-offset=1 自动增长类型的初始值auto-increment-increment=2 自动增长类型的步长
然后启动数据库
/etc/init.d/mysql startMariaDB [(none)]> select version();+---------------------+| version() |+---------------------+| 10.0.10-MariaDB-log |+---------------------+1 row in set (0.00 sec)
3. 配置Multi-Master复制
首先查一下两边当前的logfile和position（这里省略了数据初始化的过程):
--OStack01MariaDB [(none)]> show master statusG*************************** 1. row *************************** File: mysql-bin.000010 Position: 326 Binlog_Do_DB: Binlog_Ignore_DB: 1 row in set (0.00 sec)--OStack02MariaDB [(none)]> show master statusG*************************** 1. row *************************** File: mysql-bin.000005 Position: 326 Binlog_Do_DB: Binlog_Ignore_DB: 1 row in set (0.00 sec)
配置第一个节点的master为第二个节点，然后启动slave
--OStack01MariaDB [(none)]> change master to master_host='192.168.1.133',master_user='debugo',master_password='debugo',master_log_file='mysql-bin.000010',master_log_pos=326;MariaDB [(none)]> start slave;--log-error/var/log/mysqld.log140502 20:40:52 [Note] 'CHANGE MASTER TO executed'. Previous state master_host='', master_port='3306', master_log_file='', master_log_pos='4'. New state master_host='192.168.1.133', master_port='3306', master_log_file='mysql-bin.000010', master_log_pos='326'.140502 20:41:14 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.000010' at position 326, relay log './ostack01-relay-bin.000001' position: 4140502 20:41:14 [Note] Slave I/O thread: connected to master '[email protected]:3306',replication started in log 'mysql-bin.000010' at position 326
同样配置第二个节点的master为第一个节点：
--OStack02MariaDB [(none)]> change master to master_host='192.168.1.132',master_user='debugo',master_password='debugo',master_log_file='mysql-bin.000005',master_log_pos=326;MariaDB [(none)]> start slave;--log-errortail -f /var/log/mysqld.log140502 20:42:20 [Note] 'CHANGE MASTER TO executed'. Previous state master_host='', master_port='3306', master_log_file='', master_log_pos='4'. New state master_host='192.168.1.132', master_port='3306', master_log_file='mysql-bin.000005', master_log_pos='326'.140502 20:42:22 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.000005' at position 326, relay log './ostack02-relay-bin.000001' position: 4140502 20:42:22 [Note] Slave I/O thread: connected to master '[email protected]:3306',replication started in log 'mysql-bin.000005' at position 326
4. 复制测试
完成上面的配置后，进行一下简单的测试。分别从两个节点写入数据，并在另一节点验证。
--OStack01MariaDB [(none)]> use test;Database changedMariaDB [test]> create table t(name varchar(10));Query OK, 0 rows affected (0.02 sec)MariaDB [test]> set autocommit=0MariaDB [test]> show variables like "autocommit";+---------------+-------+| Variable_name | Value |+---------------+-------+| autocommit | OFF |+---------------+-------+MariaDB [test]> insert into t values ('debugo');Query OK, 1 row affected (0.00 sec)MariaDB [test]> insert into t values ('mongo');Query OK, 1 row affected (0.00 sec)MariaDB [test]> commit;--OStack02MariaDB [test]> show tables;+----------------+| Tables_in_test |+----------------+| t |+----------------+1 row in set (0.00 sec)MariaDB [test]> select * from t;+--------+| name |+--------+| debugo || mongo |+--------+2 rows in set (0.00 sec)
由此可见，从OStack01节点执行的DDL/DML改变被成功写入到了OStack02中。下面OStack02->OStack01测试：
--OStack02MariaDB [test]> delete from t where name='mongo';Query OK, 0 rows affected (0.00 sec)MariaDB [test]> commit;Query OK, 0 rows affected (0.00 sec)--OStack01MariaDB [test]> select * from t;+--------+| name |+--------+| debugo |+--------+1 row in set (0.00 sec)
^^
Reference:
http://www.mysqlperformanceblog.com/2013/10/02/mysql-5-7-multi-source-replication/
http://www.cnblogs.com/simplelogic/p/3550960.html
http://www.mysqlperformanceblog.com/2009/11/18/how-innodb_open_files-affects-performance/
http://heylinux.com/archives/2367.html
http://qdjalone.blog.51cto.com/1222376/1314962
本文出自：http://debugo.com, 原文地址：http://debugo.com/mariadb-10-multimaster/, 感谢原作者分享。
转载请注明原文链接：MariaDB 10 Multi-Master Replication 测试
