High-performance MySQL main memory architecture

MySQL Replication(master and Slave Fundamentals and Configuration)

master-Slave MySQL working principle:1: Process:(1) MySQL Replication (replication) is an asynchronous replication that replicates from one MySQL node to another MySQL node. The implementation of the entire replication operation is mainly done by three processes, of which two processes are in slave (SQL process and IO process) and another process is on the Master (IO process). (2) To implement replication, you must first open the binary log function on the master side, otherwise it cannot be implemented. Because the entire replication process is actually a variety of operations that are logged in the execution log that slave obtains the log from the master side and then executes it in its own full sequence. 2: The basic process of copying is as follows:(1) Slave The above IO process connects to master, and requests the log content from the specified location (or from the beginning of the log) to the designated log file;(2) master receives a request from the IO process from the slave, reads the log information after the specified location of the log according to the requested information through the IO process responsible for the replication, and returns the IO process to the slave. In addition to the information contained in the log, the returned information includes the name of the Bin-log file returned to the master side and the location of the Bin-log;(3) after the slave IO process receives the information, the received log content is added to the end of the Relay-log file on the slave side, and the file name and location of the Bin-log read to the master side are recorded in the Master-info file. So that the next time you read the high-speed master "I need to start from somewhere in the Bin-log log content, please send me";(4) Slave's SQL process detects new additions to the relay-log and immediately resolves the contents of Relay-log as executable content at the real execution time on the master side and executes on its own. 3: Master-Slave conclusion(1) The master-slave database is not real-time synchronization, even if the network connection is normal, there is an instant, master-slave data inconsistency.
(2) If the master-slave network is disconnected, from the network after normal, batch synchronization.
(3) If you modify the data from, it is most likely from the execution of the main Bin-log error and stop synchronization, this is a very dangerous operation. So in general, be very careful to modify the data from above.
(4) There is also a MySQL configuration is dual master, mutual main from the configuration, as long as the two sides of the modification does not conflict, can work well.
(5) If the need for more than the host, you can use a ring configuration, so that any one node changes can be synchronized to all nodes.

4: Build MySQL master server (1) Lab environment Operating system: Red Hat Enterprise Linux Server Release 6.4 database version: 5.6.21 (2) host Address: master:192.168.10.130slave:192.168.10.120 (3) Install data MySQL database slightly (4) Configure Master's my.cnf file [mysqld] Log_bin = Mysql-bin #开启binlog日志basedir =/usr/local/mysql #指定mysql的安装目录datadir =/data/mysql #指定 MySQL database data storage location port = 3306 #指定端口, the default is 3306, if a host has two databases need to specify a different port server_id = 1 #指定s Erver-id must be different from the Server-id on the slave side bind-address = 0.0.0.0 #默认是127.0.0.1, change to 0.0.0.0, otherwise slave will not be able to link to Masterexpire_logs_                      Days = Ten #终止日志的时间, by day, by default is 30 days max_binlog_size = 100M #存放日志最大容量sync-binlog=1 #允许日志同步 (5) Restart the MySQL service [[[email protected] ~]# service mysqld restart (6) log in to the MySQL database locally, Authorize slave and view the Binlog log and offset currently in use mysql> grant replication client,replication slave on * * to ' repluser ' @ ' 192.168.10.120 ' Identified by ' 123456 '; semantics: Allows the user to Repluser in the 192.168.10.120 host with password 123456, log in to the native database to synchronize data; (7) View master Status mysql> show Master Status \g; *************************** 1. Row ***************************file:mysql-bin.000015 #binlog日志Position: 211 #偏移量Binlog_D O_db:binlog_ignore_db:executed_gtid_set:1 row in Set (0.00 sec) error:no Query specified error display: Mysql>show Master Status ; Empty Set (0.02 sec) queries if the above hint indicates a problem with the configuration, (8) Configure the slave server my.cnf file [mysqld]basedir =/usr/local/mysqldatadir =/data/ Mysqlport = 3306server_id = 2 #指定server-id, must be different from the Server-id on the master side Sync-binlog=1 # Allow log synchronization Read-only=1 #设置只读 (9) Restart MySQL service [[email protected] ~]# service mysqld restart (10) Log on locally MySQL number According to the library, specify the primary server from the server and turn on the slave feature to do the following must turn off the slave feature mysql> stop slave;query OK, 0 rows Affected (0.00 sec) mysql> Change Master to master_host= ' 192.168.10.130 ', #指定主服务器地址-master_port=3306, #指定端口, default is 3306, do not need To specify master_user= ' Repluser ', #指定同步时使用的用户名, master_password= ' 123456 ', #指定同步用户的密码-ma Ster_log_filE= ' mysql-bin.000015 ', #指定当前的主服务器使用的binlog日志-master_log_pos=120; #指定当前主服务器上的位偏移量Query OK, 0 rows Affected (0.00 sec) mysql> start Slave;query OK, 0 rows Affected (0.00 sec) (11) See if slave Normal work mysql> show slave status \g;*************************** 1. Row *************************** slave_io_state:waiting for master to send event Master_hos t:192.168.10.130 master_user:repluser master_port:3306 connect_retry: master_log_file:mysql-bin.000015 read_master_log_pos:120 Relay_log_file:localhos t-relay-bin.000002 relay_log_pos:283 relay_master_log_file:mysql-bin.000015 Slave_io_r Unning:yes Slave_sql_running:yes (12) test creates WQL data in master mysql> create database WQL; Query OK, 1 row Affected (0.00 sec) mysql> Show databases;+--------------------+| Database |+--------------------+| Information_Schema | | MySQL | | Performance_schema | | Test | | WordPress | | WQL |+--------------------+6 rows in Set (0.00 sec) on slave data to see if there is a database mysql> show databases;+------------- -------+| Database |+--------------------+| Information_schema | | MySQL | | Performance_schema | | Test | | WQL |+--------------------+5 rows in Set (0.00 sec)

MySQL master-slave synchronization is completed and can work properly, but there are a few points to note:

(1) Set the slave server when you specify its home server to be separated by commas after each item
(2) If you need to change the slave server's primary server settings to stop the slave service before changing, use the command stop slave to stop the slave service
(3) Only the state of slave_io_running and slave_sql_running two processes is yse to indicate that the slave service is working properly, otherwise it will indicate that it is working abnormally.
The reason for the slave_io_running process status is no:
(1) Not connected to the primary database server (physical line, security settings issues, authorization issues)
(2) Inconsistencies between the current Binlog log name and the Pos point and the primary server
Slave_sql_running The process status is no: There is no inconsistency between the library and table or table structure on the primary database from the server.
13: How master-slave replication improves reliability

(1) Master-slave One-way replication, from the server just real-time save a copy of the master server. When the primary server fails, you can switch to continue the query from the server, but it cannot be updated.

(2) If two-way replication is used, that is, both the MySQL server as the primary server and the slave server. Both can perform the update operation and load balance, and the other party will not be affected when one side fails. However, two-way replication can cause a failure unless it is guaranteed that any order of update operations is safe.

Note: If have the operation and the preparation question in time contact; qq:1078956079/1648111701

High-performance MySQL main memory architecture