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

This article mainly introduces why mysql should use the master-slave model, the content of the article is carefully selected and edited by the author, with a certain pertinence, the reference significance for everyone is still relatively great, the following with the author to understand why mysql should use the master-slave model.

The principle of master-slave replication: the operations recorded in the binary log (binlog) on the master cloud server (master) can be replayed in the relay log (relaylog) on the slave cloud server (slave), thus realizing data synchronization and ensuring data consistency.

Prerequisites for master-slave replication:

1. The data of the primary CVM needs to be fully backed up and recovered from the CVM.

two。 Enable secret-free ssh login

3. Open the binary log on the primary CVM, and set the server_id,sync_binlog parameter to 1.

4. Open the relay log from the CVM, write to the server_id, and open the read lock (only readable but not writable from the CVM)

From the cloud server to replication related threads (show slave status\ G)

Slave_IO_Running: Yes

IO_thread: binary log used to connect to Master, monitor, and receive Master

Slave_SQL_Running: Yes

SQL_thread: used to monitor, read and replay the log information of the trunk log and write the data to the database

According to the thread copied from the cloud server, we can know that master-slave replication is that all operations on master are recorded in binlog, and then IO_thread on slave copies the contents of binlog records in master to local relaylog, and finally SQL_thread replays the contents of relaylog records to achieve the goal of data consistency.

1. First of all, we need master and slave to communicate without secret.

two。 Modify the main configuration files of master and slave

Mysql main configuration file for Master: (default / etc/my.cnf)

[mysqld] datadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.sock# Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-links=0# Settings user and group are ignored when systemd is used.# If you need to run mysqld under a different user or group # customize your systemd unit file for mariadb according to the# instructions in http://fedoraproject.org/wiki/Systemd innodb_file_per_table=ONskip_name_resolve=ONlog_bin=/var/lib/mysql/binlog # enable binary log server _ id=101 # assign an idsync_binlog=1 # binary log synchronization to disk innodb_flush_log_at_trx_commit=1 # after each transaction is executed Write in time to disk [mysqld_safe] log-error=/var/log/mariadb/mariadb.logpid-file=/var/run/mariadb/mariadb.pid # # include all files from the config directorywritten written dedir / etc/my.cnf.d

Main configuration file for mysql for Slave (default / etc/my.cnf)

[mysqld] datadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.sock# Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-links=0# Settings user and group are ignored when systemd is used.# If you need to run mysqld under a different user or group,# customize your systemd unit file for mariadb according to the# instructions in http://fedoraproject.org/wiki/Systemdinnodb_file_per_table=ONskip_name_resolve=ONserver_id=201 # assigns a server_idread_only=ON # to open the read lock Relay_log=slavelog # Open the slave log [mysqld_safe] log-error=/var/log/mariadb/mariadb.logpid-file=/var/run/mariadb/mariadb.pid # # include all files from the config directorylogging administrator dedir / etc/my.cnf.d

3. Start master, make a full backup of all databases, and replay them on slave

[root@master] # mysqldump-uroot-hlocalhost-- all-databases-p > alldata.sqlEnter password: [root@master ~] # scp alldata.sql 172.16.75.2:/alldata.sql 100% 2803KB 22.1MB/s 00:00 [root@slave ~] # mysql-uroot-p

Grant replication slave on *. * to 'repuser'@'%' identified by' reppass';MariaDB [(none)] > show master logs +-+-+ | Log_name | File_size | +-+-+ | binlog.000001 | 30379 | | binlog.000002 | 1038814 | binlog.000003 | 9598 | binlog.000004 | 647 | | binlog.000005 | 285 | binlog.000006 | 720 | | binlog.000007 | | 264 | | binlog.000008 | 264 | | binlog.000009 | 264 | | binlog.000010 | 264 | | binlog.000011 | 12140997 | +-+-+ 11 rows in set (0.00 sec) |

6. Use the authorized user on the slave to specify the attribute information of the master and start the replication thread

MariaDB [(none)] > change master to master_host='172.16.75.1',master_user='repuser',master_password='reppass',master_port=3306,master_log_file='binlog.000011',master_log_pos=12140997; MariaDB [(none)] > start slave MariaDB [(none)] > show slave status\ gateway * 1. Row * * Slave_IO_State: Waiting for master to send event Master_Host: 172.16.75.1 Master_User: repuser2 Master_Port: 3306 Connect_Retry: 60 Master_Log_File: binlog.000011 Read_Master_Log_Pos: 12140997 Relay_Log_File: slavelog.000018 Relay_Log_Pos: 12141278 Relay_Master_Log_File: binlog.000011 Slave_IO_Running: Yes Slave_SQL_Running : Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 12140997 Relay_Log_Space: 12141846 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1011 row in set (0.00 sec)

At this point, the master-slave model has been built, and when we perform data operations on master, records will also be made on slave and replayed into local data.

Validation: create a datasheet on master and view it on slave

Master side:

MariaDB [hellodb] > create table stu_info (SID int auto_increment not null primary key); Query OK, 0 rows affected (0.10 sec) MariaDB [hellodb] > desc stu_info +-+ | Field | Type | Null | Key | Default | Extra | +-+-- -+ | SID | int (11) | NO | PRI | NULL | auto_increment | +-+-+ 1 row in set (0.00 sec)

Slave side:

MariaDB [(none)] > show tables from hellodb +-+ | Tables_in_hellodb | +-+ | classes | | coc | | courses | | scores | | students | | teachers | | toc | +-+ 7 rows in Set (0.00 sec) MariaDB [(none)] > desc hellodb.stu_info +-+ | Field | Type | Null | Key | Default | Extra | +-+-- -+ | SID | int (11) | NO | PRI | NULL | auto_increment | +-+-+ 1 row in set (0.03 sec)

After reading the above about why mysql uses the master-slave model, many readers must have some understanding. If you need to get more industry knowledge and information, you can continue to follow our industry information column.

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.