MySQL Master-slave replication implementation

The last mention of using the thinkphp framework to achieve the database read and write separation, now to briefly talk about MySQL master-slave replication.

Form

• A master one from (i.e. the form to be implemented here)
• Primary master replication
• A master more from
• Multi-master one from (MySQL5.7 start support)
• Replication at the Cascade level

Figure from the Internet

Conditions

• Main Library Open Binlog log (set log-bin parameter)
• Master and slave Server-id different (this should be careful)
• Connect to the main library from the library server

Principle

• Generate two threads from the library, one I/O thread, and one SQL thread;
  
• The I/O thread requests the binlog of the main library and writes the resulting binlog log to the relay log (trunk log) file;
• The main library generates a log dump thread to path Binlog from the library I/O line;
• SQL thread, will read the log in the relay log file, and parse into concrete operation, to achieve the master-slave operation consistent, and the final data consistent;

Steps

• Add the following code to the primary database configuration file
• [Email protected] ~]# VIM/ETC/MY.CNF

Log-bin=mysql-bin//The MySQL binary log is named mysql-binbinlog_format=mixed//binary log format, there are three kinds: statement/row/mixed, the specific difference does not explain more, Here use mixedserver-id=111//set a unique ID for the server to differentiate, here use the last one of the IP address to act as Server-id

• After the configuration is complete, save and restart the primary MySQL database
• In the same operation from the database, not the same is server-id to write the main database, which is written in server-id=110;
• Save and restart from MySQL database
• Log in to the primary MySQL database, where the primary database is assigned an account from the database, which is used to share the primary database from the database.

• mysql> GRANT replication Slave on * * to ' slave ' @ '% ' identified by ' 123456 ';
  Query OK, 0 rows Affected (0.00 sec)

• View information for the primary server bin log (these values are logged after execution, and then do not do anything to the primary server until you have finished configuring the slave server. Because these two values change each time the database is manipulated);

• mysql> show master status;
  +------------------+----------+--------------+------------------+-------------------+
  | File | Position | binlog_do_db | binlog_ignore_db | Executed_gtid_set |
  +------------------+----------+--------------+------------------+-------------------+
  | mysql-bin.000001 | 315 | | | |
  +------------------+----------+--------------+------------------+-------------------+
  1 row in Set (0.00 sec

• Enter from database
• If you have previously configured master-slave replication, you must first close the slave
• Close command: Stop slave
• configuration start: Change Master to Maste R_host= ' 192.168.33.110 ', master_user= ' slave ', master_password= ' 123456 ',
            master_log_file= ' mysql-bin.000001 ', master_log_pos=315;
• parameter explanation:

master_host= ' 192.168.33.110 '//Set the IP address of the primary server to be connected

master_user= ' slave '//set the user name of the primary server to connect to

master_password= ' 123456 '//Set the password of the primary server to be connected

Master_log_file= ' mysql-bin.000001 '//sets the log name of the bin log of the primary server to be connected, which is the information given by the show slave status command for the file column name

master_log_pos=315//sets the record location of the bin log of the primary server to be connected, that is, the information position column names obtained by the show slave Status Command, (note here that the last item does not need to be quoted.) Otherwise, the configuration fails)

Restart the MySQL service from the database configuration complete

• mysql> start slave;
  Query OK, 0 rows affected, 1 Warning (0.00 sec)

• Mysql> Show Slave status \g
  1. Row ***************************
  Slave_io_state:waiting for Master to send event
  master_host:192.168.33.110
  Master_user:slave
  master_port:3306
  Connect_retry:60
  master_log_file:mysql-bin.000001
  read_master_log_pos:1036
  relay_log_file:localhost-relay-bin.000002
  relay_log_pos:1004
  relay_master_log_file:mysql-bin.000001
  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:1036
  relay_log_space:1181
  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:0
  Master_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:111
  Master_uuid:39a19e0a-7957-11e6-aa19-0800272020f4
  Master_info_file:/data/data/mysql/data/master.info
  sql_delay:0
  Sql_remaining_delay:null
  Slave_sql_running_state:slave have read all relay log; Waiting for the slave I/O thread to update it
  master_retry_count:86400
  Master_bind:
  Last_io_error_timestamp:
  Last_sql_error_timestamp:
  MASTER_SSL_CRL:
  Master_ssl_crlpath:
  Retrieved_gtid_set:
  Executed_gtid_set:
  auto_position:0
  1 row in Set (0.00 sec)

• If these two are all yes, it means success or failure; Slave_io_running:yes;slave_sql_running:yes

Finally, you can test whether it is possible ~ ~ ~

MySQL Master-slave replication implementation