Test MariaDB10Multi-MasterReplication

MySQL5.7 and MariaDB10 provide the Multi-Master replication function and improve the replication performance. Compare the configuration of Master-Standy in the previous article. This article will test the configuration of Multi-Master. Multi-Master replication is generally a circular replication. data can be copied to other hosts on any host. 1. Install MariaDBmariaDB as Re

MySQL 5.7 and MariaDB 10 provide the Multi-Master replication function and improve the replication performance. Compare the configuration of Master-Standy in the previous article. This article will test the configuration of Multi-Master. Multi-Master replication is generally a circular replication. data can be copied to other hosts on any host. 1. Install MariaDB. mariaDB is Re

MySQL 5.7 and MariaDB 10 provide the Multi-Master replication function and improve the replication performance. Compare the configuration of Master-Standy in the previous article. This article will test the configuration of Multi-Master.
Multi-Master replication is generally a circular replication. data can be copied to other hosts on any host.

1. Install MariaDB

MariaDB provides yum source installation for Redhat/CentOS. Log on to the following link and select the repo configuration for the corresponding platform:
Https://downloads.mariadb.org/mariadb/repositories/#mirror=tsinghua
In this case, I have two CentOS 6.5 virtual machines:
OStack01 192.168.1.132
OStack02 192.168.1.20.
The obtained repo information is as follows:

# 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

After configuring the repo, use yum to install it. Here, you also need to install the MariaDB-client package to have client tools such as mysql and 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

Initialize and start the database.

# 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. configuration parameter file

In the parameter file/etc/my. cnf, in addition to configuring different server-IDs, the following configuration is also very useful.
Vim/etc/my. cnf
Log_bin = mysql-bin
thread_concurrency = 2
Thread_concurrency depends on the number of CPUs on the server, which is usually CPU # * 2;
innodb_file_per_table = 1
innodb_open_files=800
By default, InnoDB stores all the table data of the InnoDB engine in a shared space: ibdata1, which makes it uncomfortable. When adding or deleting databases, the ibdata1 file does not automatically contract, the backup of a single database will also become a problem. Generally, you can only use mysqldump to export data and then import the data to solve this problem.
When the innodb data volume is large enough, it is no longer appropriate to share tablespace files. In this case, you need to modify the [mysqld]-innodb_file_per_table parameter in the mysql configuration file to set InnoDB to an independent tablespace mode. In this way, each table in each created database will generate a data space.
If you want to use an exclusive tablespace for an existing table, you need to alter table xxx engine = innodb and re-generate the table.
Using an independent tablespace has the following advantages:
1. Each table has its own independent tablespace.
2. Data and indexes of each table are stored in its own tablespace.
3. A single table can be moved in different databases.
4. space can be recycled (table space cannot be recycled automatically in the drop/truncate table operation mode)
5. For tables that use independent tablespaces, after frequent delete operations, the tablespace fragments do not cause serious performance loss and can be separately optimized later.
Disadvantages:
A single table is larger than a shared space. Shared tables have some advantages in Insert operations.
View Parameters
show variables like ‘%per_table%’;
The default number of innodb_open_files opened by innodb is 300. When innodb_file_per_table is enabled, this value should be large enough.

relay-log=relay-binlogStorage location and naming method of relay logs
auto-increment-offset=1Initial Values of auto-increment types
auto-increment-increment=2Step Size of the automatic growth type

Then start the database

/etc/init.d/mysql startMariaDB [(none)]> select version();+---------------------+| version()           |+---------------------+| 10.0.10-MariaDB-log |+---------------------+1 row in set (0.00 sec)

3. Configure Multi-Master Replication

First, check the current logfile and position on both sides (the data initialization process is omitted here ):

--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)

Configure the master of the first node as the second node, and then start 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 'debugo@192.168.1.133:3306',replication started in log 'mysql-bin.000010' at position 326

Configure the master of the second node as the first node:

--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 'debugo@192.168.1.132:3306',replication started in log 'mysql-bin.000005' at position 326

4. Copy Test
After completing the preceding configuration, perform a simple test. Write data from two nodes and verify the data on the other node.

--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)

The DDL/DML changes executed from the OStack01 node are successfully written to OStack02. The following OStack02-> OStack01 test:

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

This article from: http://debugo.com, original address: http://debugo.com/mariadb-10-multimaster/, thanks to the original author to share.