MariaDB 10 Multi-Master Replication 测试
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-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 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/, 感谢原作者分享。
推荐阅读
-
MariaDB10和MySQL5.6社区版压力测试
-
MariaDB 10 Multi-Master Replication 测试
-
MariaDB10和MySQL5.6社区版压力测试
-
MariaDB10之并行复制--延迟测试结果
-
MariaDB10之并行复制--延迟测试结果
-
mariadb 10 多源复制(Multi-source replication) 业务使用场景分_MySQL
-
MariaDB10和MySQL5.6社区版压力测试
-
MariaDB10和MySQL5.6社区版压力测试
-
mariadb 10 多源复制(Multi-source replication) 业务使用场景分_MySQL