Go to the mysql homepage and cover all the articles about how good mysql 5.6 is. Later, I browsed the 5.6 update description, saying that replication was enhanced, and some people tested to enable replication, which had little impact on performance. Unlike before, the impact on performance was obvious. But better performance? That is called mysql China website test. On the official website, we can copy multiple threads. I believe it.
However, the old configuration method on the Internet fails to be used to configure the master-slave mode, and the service cannot start. The pid file cannot be found, and the error has been forgotten ~~ Sorry. You can download the latest installation documents from the official website in full English. Finally, the configuration was completed one hour ago, in the dual-master-slave mode. Master = master.
Sort out the configuration methods.
Install mysql5.6.9 (the source code download website does not provide the latest version 5.6.10, and I do not want to install the RPM package, you know ). The installation is skipped here. You only need to check the installation prompt of the INSTALL file after decompression.
I installed mysql in the/usr/local/mysql directory. After installation, there is a my. cnf file under the/usr/local/mysql directory.
This is the configuration file. When you open it, there will be a line...
----------------- Let's start configuring -------------
Two servers: mysql-m1 192.168.0.140 mysql-m2 192.168.0.141
Open the my. cnf file for the mysql-m1 and add the following code:
Binlog-format = ROW log-slave-updates = true gtid-mode = on # GTID only enforce-gtid-consistency = true # GTID only master-info-repository = TABLE relay-log -info-repository = TABLE sync-master-info = 1 slave-parallel-workers = 2 binlog-checksum = CRC32 master-verify-checksum = 1 slave-SQL-verify-checksum = 1 binlog-rows-query-log_events = 1server-id = 1 report-port = 3306 port = 3306 log-bin = binlog report-host = 192.168.0.140
Someone must be curious. Why should I add this code? Okay, I don't know. The official team just said that. Joke ). I wrote the original meaning of each parameter: • binlog-format: row-based replication is selected in order to test all of the MySQL 5.6 optimisations • log-slave-updates, gtid-mode, enforce-gtid-consistency, report-port and report-host: used to enable Global Transaction IDs and meet the associated prerequisites • master-info-repository and relay-log-info-repository: are turned on to enable the crash-safe binlog/slave functionality (storing the information in transactional tables rather than flat files) • sync-master-info: set to 1 to ensure that no information is lost • slave-parallel-workers: sets the number of parallel threads to be used for applying replicated ed replication events when this server acts as a slave. A value of 0 wocould turn off themultithreaded slave functionality; if the machine has a lot of cores and you are using databases within the server then you may be want to increase this value in order to better exploitmulti-threaded replication • binlog-checksum, master-verify-checksum and slave-SQL-verify-checksum: used to enable all of the replication checksum checks • binlog-rows-query-log-events: enables informational log events (specifically, the original SQL query) in the binary log when using row-based replication-this makes troubleshooting simpler • log-bin: the server cannot act as a replication master unless binary logging is enabled. if you wish to enable a slave to assume the role of master at some point in the future (I. e. in the event of a failover or switchover), you also need to configure binary logging. binary logging must also be enabled on the slave (s) when using Global Transaction IDs. • server-id: The server_id variable must be unique amongst all servers in the replication topology and is represented by a positive integer value from 1 to 232
Okay. What do the above parameters mean.
Next, we also set the second server:
Binlog-format = ROWlog-slave-updates = truegtid-mode = on # GTID onlyenforce-gtid-consistency = true # GTID onlymaster-info-repository = TABLErelay-log-info-repository = TABLEsync-master-info = 1slave-parallel-workers = 2binlog-checksum = CRC32master-verify-checksum = worker = 1server-id = 2report-port = 3306 port = 3306log-bin = binlogreport-host = 192.168.0.141 note, server-id = 2. In addition, r Change eport-host.
After the two configuration files are modified, restart the server.
After restarting the server, log on to the second server mysql-m2 to log on to mysql
Mysql-u root-p
After entering the user name and password:
> Change master to MASTER_HOST = 192.168.0.140, MASTER_USER = 'repl _ user', MASTER_PASSWORD = 'Billy ';
> Start slave;
In this way, the master-slave mode is ready.
--------------------------------- On the first server, set the remote Logon account: log on to the mysql server first:> Grant all privileges on *. * to 'admin' @ '%' identified by '000000' with grant option; the account and password are in red. Similarly, the second server does the same operation.
Then, we create a table test synchronization under the test database of the master server mysql-m1: log on to the mysql server: mysql-u root-p> use test; (after installation, mysql is provided by default).> Create table abc (a int, B int, c int );
Insert data after creation.> Insert into abc values (, 3); execute multiple times and then select * from abc; check whether the data is inserted. (I inserted several rows myself ).
Mysql> select * from acc; + ------ + | a | B | c | + ------ + | 1 | 2 | 3 | 1 | 2 | 3 | 1 | 2 | 3 | 1 | 2 | 3 | 1 | 2 | 3 | 1 | 2 | 3 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | + ------ + login mysql-m2, check whether data is synchronized. After synchronization, it is OK.
------------------------------------- The official document only describes the master-slave mode. I checked it. To use the dual-master mode, you must enable the log-slave-updates = true option.
I looked at the configuration files on both servers.
Then, I tried it myself.
Log on to the master server-mysql-m1
Log on to mysql ---- mysql-u root-p and enter the password
Execute:> change master to MASTER_HOST = 192.168.0.141, MASTER_USER = 'admin', MASTER_PASSWORD = '2016 ';
> Start slave;
I did not expect it, but I did. No error was reported.
> Show slave status \ G;
Both servers can query the information.
============================== Summary ============================== this official document I understand. It gives each slave the opportunity TO become a master. If a master goes down, execute:> change master to MASTER_HOST = 192.168.0. *, MASTER_USER = 'repl _ user', MASTER_PASSWORD = 'Billy ';
> Start slave; to change an IP address, you can CHANGE any SLAVE server TO a host. After the host is started, run:> change master to MASTER_HOST = 192.168.0.MASTER _ IP, MASTER_USER = 'repl _ user', MASTER_PASSWORD = 'Billy ';
> Start slave.
However, I really don't know the functions similar to heartbeat ~~~~ Unlike service interruption, do I need to perform heartbeat on the master?
This article is from the blog "yongpan peak" and will not be reposted!
