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

Learn to build master-slave replication of mysql 5.7.19 version of MySQL today

Encountered some potholes, specific sharing:

The master and slave servers of mysql are completed by cloning virtual machines, resulting in the need to modify the auto.cnf file like uuids.

If you want to add the configuration of connecting master to / etc/my.cnf on slave, it will always cause the mysqld service to fail to start. See the error log and say that the option is not supported. The reason is that the method of adding this configuration to the configuration file has been eliminated in 5.7.19.

Here are some of the notes, which I would like to share.

Mysql > show variables like "binlog%"

| | binlog_format | ROW |

=

How to enable programs to read data from both master and slave servers at the same time:

1. If you write it directly in the program, the business will visit the MySQL server ip and be dead. -"it's stupid, it's very troublesome to maintain, and I need the assistance of the developer."

Different businesses, different servers

A-"MYSQL (A)

B-MYSQL (B)

two。 Same business, different servers. Use domain name resolution to achieve read-write separation. -"it's relatively simple. Just modify the ip corresponding to the domain name.

Write-"Lord"

Wdb.abc.com-> 132.1.1.5 Master

Read-"Master, subordinate"

You can use load balancer to resolve domain names

Rdb.abc.com-> 132.1.1.1 from

Rdb.abc.com-> 132.1.1.2 from

Rdb.abc.com-> 132.1.1.3 from

Rdb.abc.com-> 132.1.1.4 from

Rdb.abc.com-> 132.1.1.5 Master

3. Middleware is used to achieve the separation of reading and writing. Oneproxy--- "Hangzhou civilian Software-Wang Guangyou"

There is a configuration file in the middleware that reads to those servers and writes to that server.

Open source: mycat

=

Master-slave replication-"paves the way for the separation of reading and writing. -"the role of improving performance.

=

MySQL replication replication

Master slave

Write read

=

Clone clone

1. Turn off the computer first

=

Prerequisites for master-slave replication:

The data of the two MySQL servers are the same.

=

Configure the ip addresses of master and slave, not the same, and modify the hostnames to mysql_master and mysql_slave

Mysql > grant replication slave on *. * to 'backup'@'192.168.0.139' identified by' 123456'

Mysql > CREATE USER 'repl'@'%.mydomain.com' IDENTIFIED BY' slavepass'

Mysql > GRANT REPLICATION SLAVE ON *. * TO 'repl'@'%.mydomain.com'

=

/ etc/my.cnf

Server-id=2

Log-bin

Master-host=192.168.0.137

Master-user=backup

Master-password=123456

Master-port=3306

It's not feasible at the moment. It's always wrong.

The official explanation given by mysql:

Options for Obsolete Replication Slave Options obsolete master-slave replication

The following options are removed in MySQL 5.5. If you attempt to start mysqld with any of these

Options in MySQL 5.5, the server aborts with an unknown variable error. To set the replication

Parameters formerly associated with these options, you must use the CHANGE MASTER TO...

Statement (see Section 13.4.2.1, "CHANGE MASTER TO Syntax").

The options affected are shown in this list:

?-master-host

?-master-user

?-master-password

?-master-port

?-master-connect-retry

?-master-ssl

?-master-ssl-ca

?-master-ssl-capath

?-master-ssl-cert

?-master-ssl-cipher

?-master-ssl-key

=

Enter the command in the database from the server:

Mysql > change master to master_host='192.168.0.137', master_user='backup', master_password='123456', master_port=3306

Mysql > START SLAVE

Query OK, 0 rows affected (0.00 sec)

Mysql >

Slave_IO_Running: the No IO thread cannot be started

Slave_SQL_Running: Yes

Error message:

Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work

The reason is that MySQL's server UUID is the same.

[root@mysql-slave mysql] # pwd

/ var/lib/mysql-"stored in this directory

[root@mysql-slave mysql] #

[root@mysql-slave mysql] # vim auto.cnf modifies the uuid in this file

[auto]

Server-uuid=7fce030c-9f7b-11e7-b09f-000c29e2a2e4

[root@mysql-slave mysql] # service mysqld restart

Redirecting to / bin/systemctl restart mysqld.service

[root@mysql-slave mysql] #

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

=

Do not write data in slave, which will cause the SQL thread thread not to work. If it has been written, it is recommended to delete it and restart the slave process.

Mysql > start slave

=

Relay-log.info-"record the location in the binlog log of the master server last time, and the next time you copy and download the master binlog log, know where to start.

Master.info-> to connect to the primary server (the primary ip and the user and password used for the connection)

Mysql-slave-relay-bin.000006 records the binary log when the relay log is re-executed

=

1. Is there a delay in copying data between master and slave servers? Is there any data loss?

Is it better to store the master and slave servers in one computer room or two computer rooms?

two。 How to reduce latency? How to avoid data loss?

3. What is semi-synchronous replication?

4. The master server is down. how can the slave server replace the master server?

5. What role can the primary server play when it is restored? Master or slave?

6. Understand mycat and oneproxy middleware programs to achieve read-write separation

=

Contact information: 695811769@qq.com

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.