Cluster MySQL master-slave configuration (Windows and Linux editions)

Cause

Due to the need for further development and operation of the website, the host is required to operate normally over the hour, while requiring the ability to prevent database disasters. Prepare for changes to high-availability systems and improve network performance through load balancing, taking into account the level of development and volume of business in the later stages. So the first step is to consider the cluster problem of the database.

Cluster and database master-slave replication clusters

It can improve the performance and reliability of the website by working together and accomplishing the same business function through multiple physical machine servers. Different environment needs to adopt different cluster strategy, the cluster is divided into the following categories:

(1) High-availability cluster: The data storage disaster can be realized in the main form between nodes, and the resource is shared between nodes in the case of outage.

(2) Load Balancing cluster: The nodes are independent of each other, and the business operations are divided into different nodes through certain algorithms and patterns, and the resources of each node can be fully utilized to improve the expansibility and enhance the network data processing ability.

(3) Scientific Computing cluster: relative to serial computing, multiple computers execute multiple instructions to achieve a single computer can not achieve the calculation speed.

Master-slave replication

Understanding of the above high-availability cluster enables a highly available cluster to configure the server's master-slave relationship, by understanding the readiness to use MYCAT to manage the database, prior to the master-slave configuration of the database.

(1) Master-slave configuration: in order to divide the MySQL database into multiple systems, the simplest way to do this is to replicate. and MySQL built a replication method, that is, designate one or more servers as host master, and another or more servers as slave master. When Master has an update, Master writes the update to the binary file and maintains an index file trace log. When slave is connected to the primary server, slave notifies master to synchronize, and master uses the index to find the location of the last synchronization of the log file, and then sends the updated data to slave for synchronization during that time.

(2) MySQL master-slave replication support type:

A. SQL-based support: Slave executes by copying an updated SQL statement from the Matser log file to slave.

B. Row-based support: When SQL is inaccurate or records are lost, MySQL copies the changed rows directly to the slave

C. Hybrid support: Default to use a mode

(2) Advantages and disadvantages of master-slave replication

Advantages: High availability, improved fault tolerance, data distribution, load balancing.

Cons: One-way synchronization, unable to resolve host downtime problems

Master-Slave configuration

In order to facilitate the demonstration, the master-slave configuration is adopted, which is the same as the principle of single master and multiple slave. The dual-master configuration is a good way to avoid the main server downtime, the configuration is more complex, the next time a demo.

Windows Configuration test Environment

Windows 7,mysql 5.5.28, in order to avoid the trouble caused by version inconsistencies, the master-slave database with the same version of the database library

Main machine ip:192.168.1.234 Slave ip:192.168.1.244

Preparation conditions

(1) shutting down the firewall

(2) Ensure that the MySQL remote connection of the two hosts is turned on (required)

(3) 3306 port (database port number, not fixed, according to their own configuration to determine) whether public

(4) Create test database on host and slave

Host Configuration

(1) Locate the Mysql.ini file in the MySQL installation folder and add the following properties under the Mysqld tab of the file (there are many other properties that you can learn about yourself):

server-id=    #服务器id不固定, Integer can log5.5\mysql-bin  #同步日志的文件存放路径binlog- Do-db=test  #备份哪些些数据库的二进制日志 #binlog-ignore-db= ...   #也可以直接设置哪些数据库不同步

Restart MySQL after configuration is complete , at this time at C:\Program files\mysql\mysql Server 5.5\ will see Mysql-bin.index and mysql-bin.00001 two files, it is obvious that the previous file when the index file to record the second file, the second is a binary log file, each time the database restarts will produce a new log file, the index of these log files are indexed by the file name index file Maintenance, you can see the coordinates of all log files in the index file.

(2) Login to MySQL, configure login name, login, password and permissions to the slave machine

Grant replication Slave,reload,Super on * * to [E-  Mail protected]  identified by ' Qwer ';

This statement means that a remote server with IP 192.168.1.244 (slave IP) configuration login is slave, password is qwer, and Slave,super,reload permissions are given

(3) Viewing host status

Show master status;

Find the host file and postin in the slave configuration need to use, remember.

To this, the host configuration is complete.

From the machine configuration

(1) Locate the Mysql.ini file in the slave MySQL installation directory, and add the following under the Mysqld tab of the file:

server-id=88 #同样随机, guaranteed integer unique replicate-do-db=test1  #复写 (synchronous) which database

Restart the database after configuration

(2) test the remote connection of the host is successful (necessary test) if you do not immediately troubleshoot the problem, do not proceed to the next step

Mysql-uslave-h 192.168.1.234-pqwer

After the connection is successful, exit the host MySQL and enter the slave MySQL

(3) Modify (should be called for the first time) host information

stop slave;       = ' 192.168.1.234 ', master_user= ' slave ', master_ password = ' qwer ', master_log_file= ' mysql-bin.000001 ', master_log_pos= 593;

Host address, host name, host password, binary file address, just host file and position specified in the host information (above the change statement)

Note: If a warning occurs, review the log file for troubleshooting, or the subsequent contract will not succeed. After the modification is complete

Start slave;     turn on the sync connection show slave status\g;   View Master-Slave connection information

To view the synchronization status is:

The IO status in the figure indicates whether the master-slave database is successfully connected, SQL represents two simultaneous files and the slave slave is enough to connect successfully, refer to the first graph of IO and SQL

If two is yes, the synchronization configuration is successful, and then you can test it again by testing the database. You can leave a message if there are any problems.

Linux Configuration test Environment

MySQL version 5.7 Two server versions are required to be consistent in order to avoid future unpredictable problems

Host Ubuntu 16.04 ip:192.168.1.116

From the machine CentOS 6.5 ip:192.168.1.254

Preparation conditions

(1) shutting down the firewall

sudo service UFW stop

(2) Ensure the MySQL remote connection of the two servers is successful

(3) Ensure that 3306 ports can be used normally

A. See if Port 3306 is bound to the ground

Netstat-an|grep 3306

is correct, if the 127.0.0.0:::3306 indicates that the port is natively bound, in which case the configuration of the mysqld needs to be modified

Note : Here is the modification of the mysqld configuration file configuration, which is not functional only in MY.CNF

sudo vim/etc/mysql/mysql.conf.d/mysqld.cnf    into the mysqld configuration file

comment out this line of code under MYSQLD

(4) Create a database with the same name as test

Host Configuration

(1) Find my.cnf file

Unlike Windows, the Linux configuration file is my.cnf, and this file is on/etc on the default, if it is not found in the. xx/nysql/directory. If you can't find my.cnf you can enter the following command (enter this command at the root):

Find-name ' my.cnf ' #找到当前目录下my. CNF location

(2) Configuration my.cnf

If you are installing MySQL for the first time (don't know how to install it, click "MySQL installation under Linux"), The first time to enter MY.CNF will find that the file does not have any tags, this time can be in other places (official online or on the site) copy my.cnf header file or style, if too troublesome can be directly under my.cnf add a [mysqld] tag (necessary) Otherwise it will be an error. Then add the following configuration under the Mysqld tab:

server_id=66log-bin=mysql-binbinlog-do-db=test

Then restart MySQL, same as Windows.

(3) Authorization (same as Windows)

Grant replication Slave,reload,Super on * * to [E-  Mail protected]  identified by ' Qwer ';

(4) Recording host configuration

Show master status;

Slave configuration ... Same

Under the My.cnf file, under the Sqld label (as the host does not add) under the configuration:

server-id=88 replicate-do-db=test1  #复写 (synchronous) which database

Restart MySQL ...

Mysql-uslave-h 192.168.1.116-pqwer #测试主机连接stop slave;      #关闭从机同步连接change Master to Master_host= ' 192.168.1.234 ', master_user= ' slave ', master_password= ' qwer ', Master_log_ File= ' mysql-bin.000001 ', master_log_pos=593;  #填写刚刚信息start slave;     #开启同步连接show slave status\g;   #查看主从机连接信息

Synchronization status is detected after completion:

Two yes means synchronization is successful, if there is no analysis log to solve the problem, or message can also

The configuration of the above two versions is over.

Attention

In accordance with the above configuration, the host has access to the slave after DQL and DML, that is, read and write permissions, and does not have the DDL operation permissions . In other words, only the two database with the same name has the same name, changing the data of the host table will synchronize to the slave. If you need permissions to create tables, and so on, you can modify the permissions in the user table of the MySQL database in the database.

You can also use authorization statements to grant all permissions to a remote host:

Grant all privileges on * * to [email protected] identified by ' qwer '; #授予所有权限
Flush privileges; #刷新数据库

This way, operations such as tables in the primary database can also be synchronized to the library

Cluster MySQL master-slave configuration (Windows and Linux editions)