Build a master-slave replication environment for mysql databases under linuxcentos _ MySQL

There are two MySQL database servers: Master and slave. The Master is the Master server. The slave is the slave server. In the initial state, the data information in Master and slave is the same. When the data in Master changes, slave also changes accordingly, so that the master and slave have two MySQL databaseThe Master and slave servers are the Master servers. The slave servers are slave servers. In the initial state, the data information in the Master and slave is the same. When the data in the Master node changes, slave also changes accordingly, so that the master and slave data information is synchronized to achieve the purpose of backup.

The schematic diagram is as follows:

To put it simply, the principle of mysql master-slave replication is that slave updates the SQL statements executed on the master, and insert these SQL statements that change the data to be synchronized from the master, then run the command again on your machine. The data on these two database servers is exactly the same, and the SQL statements to be synchronized are stored in the bin-log file.

Here I have two machines: 192.168.1.6 (master) 192.168.1.5 (slave)

We will first start configuring the master machine

Modify the/etc/my. cnf file (mysql starts from/etc/my. cnf by default, so if you have a configuration file elsewhere, we recommend that you directly move it to the/etc directory)

[client]port = 3306socket = /usr/local/mysql/mysql.sock[mysqld]character-set-server = utf8collation-server = utf8_general_ciskip-external-lockingskip-name-resolveuser = mysqlport = 3306basedir = /usr/local/mysqldatadir = /home/mysql/datatmpdir = /home/mysql/temp# server_id = .....socket = /usr/local/mysql/mysql.socklog-error = /home/mysql/logs/mysql_error.logpid-file = /home/mysql/mysql.pidopen_files_limit = 10240back_log = 600max_connections=500max_connect_errors = 6000wait_timeout=605800#open_tables = 600#table_cache = 650#opened_tables = 630max_allowed_packet = 32Msort_buffer_size = 4Mjoin_buffer_size = 4Mthread_cache_size = 300query_cache_type = 1query_cache_size = 256Mquery_cache_limit = 2Mquery_cache_min_res_unit = 16ktmp_table_size = 256Mmax_heap_table_size = 256Mkey_buffer_size = 256Mread_buffer_size = 1Mread_rnd_buffer_size = 16Mbulk_insert_buffer_size = 64Mlower_case_table_names=1default-storage-engine = INNODBinnodb_buffer_pool_size = 2Ginnodb_log_buffer_size = 32Minnodb_log_file_size = 128Minnodb_flush_method = O_DIRECT#####################thread_concurrency = 32long_query_time= 2slow-query-log = onslow-query-log-file = /home/mysql/logs/mysql-slow.log  ## replicationserver_id=6binlog-ignore-db=mysqllog-bin=master-mysql-binbinlog_cache_size=1Mbinlog_format=mixedexpire_logs_days=7slave_skip_errors=1062[mysqldump]quickmax_allowed_packet = 32M[mysqld_safe]log-error=/var/log/mysqld.logpid-file=/var/run/mysqld/mysqld.pid

The main configuration here is the copy-related configuration. The other configuration is the configuration when mysql source code is installed. What does the specific parameters mean? You can search for them online.

## replicationserver_id=6binlog-ignore-db=mysqllog-bin=master-mysql-binbinlog_cache_size=1Mbinlog_format=mixedexpire_logs_days=7slave_skip_errors=1062

For a brief explanation, copy the relevant parameters.

Serverid is globally unique

Binlog-ignore-db = mysql replication filtering. We do not synchronize the databases that come with mysql.

Log-bin = master-mysql-bin enable the logbin function and set the logbin file name

Binlog_format = mixed hybrid replication mode. By default, statement-based replication is used. Once a statement-based replication fails, row-based replication is used.

Restart the mysql service and log on with the client.

Slave requires permissions to synchronize data on the master first, so we need to activate permissions on the master.

grant replication slave, replication client on *.* to 'root'@'192.168.1.5' identified by'root';

The account and password are both root and allow the machine 192.168.1.5 to send synchronization requests to the master. Of course, you can set them to something else. Here I just want to make it easier to remember, and too many different passwords are easy to forget.

Refresh the authorization information and view the status of the current master.

Show master status. Here I will explain that all the SQL statements about database modification are recorded in bin-log, so we will execute all the SQL statements in the bin-log every time? Certainly not. We must keep a record of where I copied the last time, and then I will start to synchronize it from this point next time, just as we will save the file after playing a standalone game and hitting 3 levels, next time we start playing from the third level.

Next, write test data to the database.

create database if not exists pcx default charset utf8 collate utf8_general_ci;use pcx;DROP TABLE IF EXISTS `fruits`;CREATE TABLE fruits(f_id    char(10)     NOT NULL,s_id    INT        NOT NULL,f_name  char(255)  NOT NULL,f_price decimal(8,2)  NOT NULL,PRIMARY KEY(f_id) )ENGINE=InnoDB DEFAULT CHARSET=utf8 ;INSERT INTO fruits (f_id, s_id, f_name, f_price)     VALUES('a1', 101,'apple',5.2),     ('b1',101,'blackberry', 10.2),     ('bs1',102,'orange', 11.2),     ('bs2',105,'melon',8.2),     ('t1',102,'banana', 10.3),     ('t2',102,'grape', 5.3),     ('o2',103,'coconut', 9.2),     ('c0',101,'cherry', 3.2),     ('a2',103, 'apricot',2.2),     ('l2',104,'lemon', 6.4),     ('b2',104,'berry', 7.6),     ('m1',106,'mango', 15.6),     ('m2',105,'xbabay', 2.6),     ('t4',107,'xbababa', 3.6),     ('m3',105,'xxtt', 11.6),     ('b5',107,'xxxx', 3.6);

Next, we will back up all the data in the database pcx.

First, we need to lock the table.

flush tables with read lock;

Next, use the mysql BACKUP command to back up data.

Finally, we sent the edu-master. SQL to the Server Load balancer.

Now let's start configuring slave (192.168.1.5)

Open the my. cnf file to start configuration.

vi /etc/my.cnf

[client]port = 3306socket = /usr/local/mysql/mysql.sock[mysqld]character-set-server = utf8collation-server = utf8_general_ciskip-external-lockingskip-name-resolveuser = mysqlport = 3306basedir = /usr/local/mysqldatadir = /home/mysql/datatmpdir = /home/mysql/temp# server_id = .....socket = /usr/local/mysql/mysql.socklog-error = /home/mysql/logs/mysql_error.logpid-file = /home/mysql/mysql.pidopen_files_limit = 10240back_log = 600max_connections=500max_connect_errors = 6000wait_timeout=605800#open_tables = 600#table_cache = 650#opened_tables = 630max_allowed_packet = 32Msort_buffer_size = 4Mjoin_buffer_size = 4Mthread_cache_size = 300query_cache_type = 1query_cache_size = 256Mquery_cache_limit = 2Mquery_cache_min_res_unit = 16ktmp_table_size = 256Mmax_heap_table_size = 256Mkey_buffer_size = 256Mread_buffer_size = 1Mread_rnd_buffer_size = 16Mbulk_insert_buffer_size = 64Mlower_case_table_names=1default-storage-engine = INNODBinnodb_buffer_pool_size = 2Ginnodb_log_buffer_size = 32Minnodb_log_file_size = 128Minnodb_flush_method = O_DIRECT#####################thread_concurrency = 32long_query_time= 2slow-query-log = onslow-query-log-file = /home/mysql/logs/mysql-slow.log  ## replicationserver_id=5binlog-ignore-db=mysqllog-bin=mysql-slave-binbinlog_cache_size = 1Mbinlog_format=mixedexpire_logs_days=7slave_skip_errors=1062relay_log=mysql-relay-binlog_slave_updates=1read_only=1[mysqldump]quickmax_allowed_packet = 32M[mysqld_safe]log-error=/var/log/mysqld.logpid-file=/var/run/mysqld/mysqld.pid

The configuration file is similar to that of the master. The main differences are as follows:

Log-bin = mysql-slave-bin enables binary logs. In fact, it doesn't matter if you don't open binary logs, because we only use slave as a server Load balancer, if your slave may be another master, you must enable it.

Relay_log = mysql-relay-bin configure the relay log to store the data synchronized from the bin-log of the master.

Restart Database Service after Configuration

service mysql restart

First, we need to create a database, because the database file we backed up does not contain commands for creating a database, so we need to manually create it.

Recover the data we backed up from the master to the slave.

Log on to the Slave Database

Enter a command in the mysql client to connect to the master

Change master,
Master_host = '192. 168.1.6 'IP address of the master host

Master_user = 'root ',

Master_password = 'root'. This is the password of the account that we just performed authorization on the master.

Master_port = 3306, master database port number

Master_log_file = 'edu-mysql-bin.000002 ',

Master_log_pos = 427, which is the position we can see through show master status

Master_connect_retry = 30;

Run the command to view the slave status. We can see that the slave has not started synchronization yet.

 show slave status\G;

Start master-slave synchronization. If you see two "yes", the synchronization is successful.

start slave;

Check the status on the master machine to view the slave connection information.

show processlist\G;

Next we will test the master-slave replication function.

Original data on the master

This is the record I inserted.

Let's go to the slave to see if there is any. If there is one, it means it is successful.

So far, mysql native supports master-slave replication. Of course, we can say that mysql master-slave replication is complete.

There is a latency in performance. The data on the master node is not synchronized to the slave without delay. Therefore, if you have high requirements on data consistency, the official mysql master-slave replication is not suitable, you can consider using other data synchronization solutions such as "Galera Cluster for MySQL". Of course, some people say that the master-slave replication officially supported by mysql is only suitable for running in Small and Medium-sized clusters.