MySQL Master-slave replication

1. Single master configuration instance 1.1 overall process:

1. Master and Slave time synchronization
2. Master turns on binary logging and authorizes a specific user to have copy permissions on the binaries
3. Slave turn on the relay log function, execute the synchronous statement, turn on the synchronization function.
   Note: When the version number is inconsistent, the master version is lower than the slave version

1.2 Master-side configuration

Edit/etc/my.cnf, add the following in the MYSQLD configuration section (restart service)

server-id=1log_bin=/data/mysql/logs/mysql-bin

Start the database and authorize it:

MariaDB [(none)]> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO ‘node2‘@‘192.168.200.%‘ IDENTIFIED BY ‘node2pass‘;MariaDB [(none)]> FLUSH PRIVILEGES;

1.3 Master-Side considerations

1. /data/mysql/logs directory needs to exist, and belongs to the user of the main, the group is running MySQL (because MySQL needs to create and write files in this directory)
2. Mysql-bin can be defined by itself.
3. The value behind the Server-id is unique and cannot be duplicated between nodes.

1.4 Slave End Configuration

Edit/etc/my.cnf, add the following in the MYSQLD configuration section (restart service)

server-id=2relay-log=/data/mysql/logs/relay-bin

Set the master node and start after starting the service

MariaDB [(none)]> change master to master_host= ' 192.168.200.41 ', master_port=3306,master_user= ' Node2 ', master_ Password= ' Node2pass ', master_log_file= ' mysql-bin.000003 ', master_log_pos = 4; MariaDB [(None)]> START SLAVE; MariaDB [(none)]> show slave status\g*************************** 1. Row *************************** slave_io_state:waiting for master to send event Master_hos              t:192.168.200.41 Master_user:node2 master_port:3306 connect_retry:60 master_log_file:mysql-bin.000003 read_master_log_pos:927 relay_log_file:relay-bin.0            00003 relay_log_pos:604 relay_master_log_file:mysql-bin.000003 Slave_io_running:yes Slave_sql_running:yes REPLICATE_DO_DB:REPLICATE_IGNORE_DB:REPLICATE_DO_TABL     E:replicate_ignore_table:replicate_wild_do_table:replicate_wild_ignore_table:               last_errno:0 last_error:skip_counter:0 Exec_master_log_pos: 927 relay_log_space:1783 Until_condition:none Until_log_file:un               til_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:1

When you view the show slave status, the slave_io_running and slave_sql_running states are yes when they are successful. You can create libraries or tables in the master node to see if they are also available from the node.

1.5 Slave Tips

2. /data/mysql/logs directory needs to exist, and belongs to the user of the main, the group is running MySQL (because MySQL needs to create and write files in this directory)
4. Relay-bin names can be defined on their own.
6. The values behind the Server-id are unique and cannot be duplicated.
7. Change master may cause the slave I/O thread to fail if the Master_log_pos location is incorrect. The error log is as follows:

   180603 16:46:49 [Note] Slave I/O thread: connected to master ‘[email protected]:3306‘,replication started in log ‘mysql-bin.000003‘ at position 760180603 16:46:49 [ERROR] Error reading packet from server: binlog truncated in the middle of event; consider out of disk space on master; the first event ‘mysql-bin.000003‘ at 760, the last event read from ‘mysql-bin.000003‘ at 760, the last byte read from ‘mysql-bin.000003‘ at 769. ( server_errno=1236)180603 16:46:49 [ERROR] Slave I/O: Got fatal error 1236 from master when reading data from binary log: ‘binlog truncated in the middle of event; consider out of disk space on master; the first event ‘mysql-bin.000003‘ at 760, the last event read from ‘mysql-bin.000003‘ at 760, the last byte read from ‘mysql-bin.000003‘ at 769.‘, Error_code: 1236

   Workaround:
   View the current binaries by show binary logs, and then through show Binlog events in ' Binlog file NAME ', locate the correct POS location and use the correct POS location when change master.

     MariaDB [(none)]> Show binary logs;+------------------+-----------+| Log_name | File_size |+------------------+-----------+|     mysql-bin.000001 | 30355 | |   mysql-bin.000002 | 1038814 | |       mysql-bin.000003 | 491 |+------------------+-----------+3 rows in Set (0.00 sec)  

   MariaDB [(None)]> show Binlog events in ' mysql-bin.000003 '; +------------------+-----+-------------+-----------+-- -----------+--------------------------------------------------------------------------------------------------+ | Log_name | Pos | Event_type | server_id | End_log_pos | Info |+------------------+---- -+-------------+-----------+-------------+--------------------------------------------------------------------- -----------------------------+|   mysql-bin.000003 | 4 |         Format_desc |         1 | 245 | Server ver:5.5.56-mariadb, Binlog ver:4 | | mysql-bin.000003 | 245 |         Query |         1 | 416 | Grant replication Slave,replication Client on * * to [email protected] ' 192.168.200.% ' identified by ' Lxkpass ' | | mysql-bin.000003 | 416 |         Query |         1 | 491 |            Flush Privileges                                                                     |+------------------+-----+-------------+-----------+ -------------+------------------------------------------------------------------------------------------------- -+3 rows in Set (0.00 sec)

   As above code: Select POS (start position) or end_log_pos (end position) can be.

MySQL Master-slave replication