MariaDB 10 Multi-Master Replication 测试
- WBOY원래의
- 2016-06-07 16:41:521164검색
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<br>
innodb_open_files=800
InnoDB 默认会将所有的数据库InnoDB引擎的表数据存储在一个共享空间中：ibdata1，这样就感觉不爽，增删数据库的时候，ibdata1文件不会自动收缩，单个数据库的备份也将成为问题，通常只能将数据使用mysqldump 导出，然后再导入解决这个问题。
当innodb数据量足够大时，共享表空间文件已经能够不合适。这时需要修改mysql配置文件的[mysqld] – 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/, 感谢原作者分享。