Shulou(Shulou.com)06/01 Report--

1. Both mysql can read and write, and they are active and standby to each other. By default, only one (masterA) is responsible for data writing, and the other (masterB) is used as backup.

2. MasterA is the main library of masterB, and masterB is the main library of masterA. They are subordinate to each other.

Inadequacies:

1. MasterB may be in a suppressed idle state (you can use it as a slave library and take charge of some queries)

two。 The slave library that provides services behind the master library can only go to masterB to synchronize data after masterB has synchronized the data first, which will certainly cause a certain degree of synchronization delay.

Build an environment

System: CentOS7

Database version: 5.6.36

MasterA address: 192.168.188.2

MasterB address: 192.168.188.3

Installation directory: / usr/local/mysql/

Data directory: / data/mysql/

Both An and B machines are installed with MySQL, and the database and tables are synchronized in advance.

Firewall configuration iptables

Add mysql classmate port (default 3306)

Vim / etc/sysconfig/iptables / / Edit the iptables file, or you can use the iptables command to add However, save the command-An INPUT-m state-- state NEW-m tcp-p tcp-- dport 3306-j ACCEPT / / add allow port 3306 to shut down selinuxvim / etc/selinux/config / / edit the SELinux configuration file via the service iptables restart / / restart iptables service SELINUX=disabled / / modify the value to disabled to set the masterA setting for shutting down the server (192.168.188 .2) vim / etc/my.cnf / / Edit my.cnf file server-id=2 / / increase server-id to 2 Generally, set the log_bin to test01/etc/init.d/mysqld restart / / restart the mysql service mysql-uroot-p123456 / / and log in to mysql. Do not copy,-u is followed by an account, and-p is followed by the password grant replication slave on *. * to 'repl'@'192.168.188.3' identified by' 123456' / / create a mysql account repl, which can only be accessed by a specified ip. You can also specify an ip range of 192.168.188%, where% is a wildcard and represents all Flush privileges; / / Refresh authorization table information flush tables with read lock; / / Lock database table temporarily unable to write service; show master status; / / View binlog file value and pos value stop slave / / turn off synchronous masterB settings (192.168.188.3) vim / etc/my.cnfserver-id=3 / / increase server-id to 3 Generally, set your own iplog_bin=test02 / / log_bin name as test02/etc/init.d/mysqld restart / / restart the mysql service mysql-uroot-p123456 / / log in to mysqlgrant replication slave on *. * to 'repl'@'192.168.188.2' identified by' 123456' / / create a user to allow 192.168.188.2 to log in to the flush privileges of this machine / / Refresh authorization table stop slave; / / turn off synchronization show master status; / / check binlog file values and pos values change master to master_host='192.168.188.2', master_user='repl', master_password='123456', master_log_file='test01.000001', master_log_pos=664383; / / Note here that both log_file and poss values correspond to A's show master status Value start slave; / / enable synchronous masterA setting change master to master_host='192.168.188.3', master_user='repl', master_password='123456', master_log_file='test02.000001', master_log_pos=664343; / / here log_file and posvalue must be the value of show master status; on B start slave; / / enable synchronous unlock tables; / / unlock the master of the write test

The T1 table is established under the test database of A, and there is a T1 table on B after query, which proves that B can synchronize the data modified by A.

Establish T2 table under B's test database, query A, have T2 table, prove that A can synchronize to B's modified data.

MasterA sets mysql-uroot-p123456 / / Log in to mysqlmysql > use test / / switch to database testmysql > show tables; / / View all the tables in the current database. There is no table Empty set (0.00 sec) mysql > create table T1 (`id` int (4), `name` char (40)); / / insert a table t1mysql > show tables / / View the current database table +-+ | Tables_in_test | +-+ | T1 | +-+ 1 row in set (0.00 sec) masterBmysql-uroot-p123456 / / Log in to mysqluse test / / switch to database testmysql > show tables; / / query synchronization to database test the T1 table appears, which proves that B can synchronize A's data. +-+ | Tables_in_test | +-+ | T1 | +-+ 1 row in set (0.00 sec) create table T2 (`id` int (4), `name` char (40)) / / create a new T2 table. If A can synchronize to B, it will prove that A can synchronize to B to switch masterA (omitting commands for logging in and switching database) mysql > show tables / / query all tables under A's test database +-+ | Tables_in_test | +-+ | T1 | | T2 | +-+ 2 rows in set (0.00 sec)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.