MariaDB10Multi-MasterReplication测试

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 = MariaDB
baseurl = http://yum.mariadb.org/10.0/centos6-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

配置好repo之后，使用yum安装即可。这里还需要安装MariaDB-client的包才有会mysql, mysqladmin等客户端工具。

# yum search MariaDB
Loaded plugins: fastestmirror, refresh-packagekit, security
Loading mirror speeds from cached hostfile
 * base: centos.ustc.edu.cn
 * epel: ftp.sjtu.edu.cn
 * extras: centos.ustc.edu.cn
 * updates: centos.ustc.edu.cn
epel/pkgtags                                             | 1.0 MB     00:07     
============================= N/S Matched: MariaDB =============================
MariaDB-Galera-server.x86_64 : MariaDB: a very fast and robust SQL database
                             : server
MariaDB-cassandra-engine.x86_64 : MariaDB: a very fast and robust SQL database
                                : server
MariaDB-client.x86_64 : MariaDB: a very fast and robust SQL database server
MariaDB-common.x86_64 : MariaDB: a very fast and robust SQL database server
MariaDB-compat.x86_64 : MariaDB: a very fast and robust SQL database server
MariaDB-connect-engine.x86_64 : MariaDB: a very fast and robust SQL database
                              : server
MariaDB-devel.x86_64 : MariaDB: a very fast and robust SQL database server
MariaDB-oqgraph-engine.x86_64 : MariaDB: a very fast and robust SQL database
                              : server
MariaDB-server.x86_64 : MariaDB: a very fast and robust SQL database server
MariaDB-shared.x86_64 : MariaDB: a very fast and robust SQL database server
MariaDB-test.x86_64 : MariaDB: a very fast and robust SQL database server
innotop.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 M
Installing for dependencies:
 MariaDB-common        x86_64        10.0.10-1.el6         mariadb         23 k
 perl-DBI              x86_64        1.609-4.el6           base           705 k
Transaction 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=mysql
Installing MariaDB/MySQL system tables in '/var/lib/mysql' ...
......
# service mysql start
Starting MySQL. SUCCESS! 
# mysql_secure_installation
Enter current password for root (enter for none): 
OK, successfully used password, moving on...
Set root password? [Y/n] Y
New 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 MariaDB
installation should now be secure.
Thanks for using MariaDB!

2. 配置参数文件

在参数文件/etc/my.cnf中，除了配置不同的server-id之外，下面的配置也是非常有用的。
vim /etc/my.cnf
log_bin = mysql-bin
thread_cOncurrency= 2
thread_concurrency视服务器的CPU数量设置，通常为CPU# * 2;
innodb_file_per_table = 1
innodb_open_files=800
InnoDB 默认会将所有的数据库InnoDB引擎的表数据存储在一个共享空间中：ibdata1，这样就感觉不爽，增删数据库的时候，ibdata1文件不会自动收缩，单个数据库的备份也将成为问题，通常只能将数据使用mysqldump 导出，然后再导入解决这个问题。
当innodb数据量足够大时，共享表空间文件已经能够不合适。这时需要修改mysql配置文件的[mysqld] &＃8211; innodb_file_per_table参数，可将InnoDB设为独立表空间模式，这样之后创建的每个数据库的每个表都会生成一个数据空间。
如果需要对现有表使用独享表空间，则需要alter table xxx engine=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 start
MariaDB [(none)]> select version();
+---------------------+
| version()           |
+---------------------+
| 10.0.10-MariaDB-log |
+---------------------+
1 row in set (0.00 sec)

3. 配置Multi-Master复制

首先查一下两边当前的logfile和position（这里省略了数据初始化的过程):

--OStack01
MariaDB [(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)
--OStack02
MariaDB [(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

--OStack01
MariaDB [(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.log
140502 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: 4
140502 20:41:14 [Note] Slave I/O thread: connected to master 'debugo@192.168.1.133:3306',replication started in log 'mysql-bin.000010' at position 326

同样配置第二个节点的master为第一个节点：

--OStack02
MariaDB [(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-error
tail -f /var/log/mysqld.log
140502 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: 4
140502 20:42:22 [Note] Slave I/O thread: connected to master 'debugo@192.168.1.132:3306',replication started in log 'mysql-bin.000005' at position 326

4. 复制测试
完成上面的配置后，进行一下简单的测试。分别从两个节点写入数据，并在另一节点验证。

--OStack01
MariaDB [(none)]> use test;
Database changed
MariaDB [test]> create table t(name varchar(10));
Query OK, 0 rows affected (0.02 sec)
MariaDB [test]> set autocommit=0
MariaDB [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;
--OStack02
MariaDB [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测试：

--OStack02
MariaDB [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)
--OStack01
MariaDB [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/, 感谢原作者分享。