欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

MariaDB 10 Multi-Master Replication 测试

程序员文章站 2022-05-26 20:08:50
...

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 复制通常是环形复制,可以在任意主机上将数据复制给其他主机。
MariaDB 10 Multi-Master Replication 测试

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] – 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