Complete implementation of Master-Slave replication Model of Database Cluster
Master-slave configuration process: see https://mariadb.com/kb/en/library/setting-up-replication/ https://dev.mysql.com/doc/refman/5.5/en/replication-configuration.html
Primary server 192.168.27.7 configuration:
[root@master ~] $vim / etc/my.cnf [mysqld] datadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.sock# Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-links=0innodb_file_per_tablelog_binserver-id=1 [root@master ~] $systemctl start mariadb.service MariaDB [(none)] > show binary logs +-+-+ | Log_name | File_size | +-+-+ | mariadb-bin.000001 | 245 | +- -+ 1 row in set (0.00 sec) [root@master ~] $mysql
< hellodb_InnoDB.sqlMariaDB [(none)]>Show binary logs +-+-+ | Log_name | File_size | +-+-+ | mariadb-bin.000001 | 7655 | +- -- + 1 row in set (0.00 sec) MariaDB [(none)] > grant replication slave on *. * to repluser@'192.168.27.%' identified by 'centos' Query OK, 0 rows affected (0.00 sec)
Service configuration from server 192.168.27.17:
From configuration on server: [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.innodb_file_per_tableserver_id=2 Note: default server_id is 0 MariaDB [(none)] > show variables like 'server_id';+-+-+ | Variable_name | Value | +-+-+ | server_id | 0 | +-+-+ 1 row in set (0.01 sec) View help: MariaDB [(none)] > help change master to CHANGE MASTER TO MASTER_HOST='master2.mycompany.com', MASTER_USER='replication', MASTER_PASSWORD='bigs3cret', MASTER_PORT=3306, MASTER_LOG_FILE='master2-bin.001', MASTER_LOG_POS=4, MASTER_CONNECT_RETRY=10 MariaDB [(none)] > CHANGE MASTER TO-> MASTER_HOST='192.168.27.7',-> MASTER_USER='repluser',-> MASTER_PASSWORD='centos',-> MASTER_PORT=3306,-> MASTER_LOG_FILE='mariadb-bin.000001',-> MASTER_LOG_POS=245,-> MASTER_CONNECT_RETRY=10 Query OK 0 rows affected (0.02 sec) MariaDB [(none)] > show slave status\ gateway * 1. Row * * Slave_IO_State: Master_Host: 192.168.27.7 Master _ User: repluser Master_Port: 3306 Connect_Retry: 10 Master_Log_File: mariadb-bin.000001 Read_Master_Log_Pos: 245 Relay_Log_File: mariadb-relay-bin.000001 Relay_Log_Pos: 4 Relay_Master_Log_File: mariadb-bin.000001 Slave_IO_Running: No thread not started Slave_SQL_Running: the No thread did not start 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: start of replication Relay_Log_Space: 245Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No can enable encryption Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: NULLMaster_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: 01 row in set (0.00 sec)
Relay log has been created [root@centos7x] $ll / var/lib/mysql/total 28724 ib_logfile0-rw-rw---- RW Feb 25 06:37 aria_log.00000001-rw-rw---- 1 mysql mysql 52 Feb 25 06:37 aria_log_control-rw-rw---- 1 mysql mysql 18874368 Feb 25 06:37 ibdata1-rw-rw---- 1 mysql mysql 5242880 Feb 25 06:37 ib_logfile0-rw-rw---- 1 mysql mysql 5242880 Feb 25 05:09 ib _ logfile1-rw-rw---- 1 mysql mysql 264 Feb 25 05:15 mariadb-bin.000001-rw-rw---- 1 mysql mysql 21 Feb 25 05:10 mariadb-bin.index-rw-rw---- 1 mysql mysql 245 Feb 25 06:38 mariadb-relay-bin.000001-rw-rw---- 1 mysql mysql 27 Feb 25 06:38 mariadb-relay-bin.index-rw-rw---- 1 mysql mysql 84 Feb 25 06:38 master.infodrwx- 2 mysql mysql 4096 Feb 25 05:09 mysqlsrwxrwxrwx 1 mysql mysql 0 Feb 25 06:37 mysql.sockdrwx- 2 mysql mysql 4096 Feb 25 05:09 performance_schema-rw-rw---- 1 mysql mysql 52 Feb 25 06:38 relay-log.infodrwx- 2 mysql mysql 6 Feb 25 05:09 test
Start the replication thread on the server:
MariaDB [(none)] > start slave;ERROR 2006 (HY000): MySQL server has gone awayNo connection Trying to reconnect...Connection id: 4Current database: * NONE * * Query OK, 0 rows affected, 1 warning (0.00 sec) MariaDB [(none)] > show databases +-+ | Database | +-+ | information_schema | | hellodb | | mysql | | performance_schema | | test | +-+ MariaDB [(none)] > show slave status\ G* * 1. Row * * Slave_IO_State: Waiting for master to send event Master_Host: 192.168.27.7 Master_User: repluser Master_Port: 3306 Location of logs read by Connect_Retry: 60 Master_Log_File: mariadb-bin.000002 Read_Master_Log_Pos: 245 Relay_Log_File: mariadb-relay-bin.000005 Relay_Log_Pos: 531 Relay_Master_Log_File: mariadb-bin.000002 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: 245 location of the log executed Relay_Log_Space: 1113 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: Time lag behind 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: 11 row in set (0.00 sec)
Read and write operations of the primary server:
Log location of the primary server MariaDB [(none)] > show master logs +-+-+ | Log_name | File_size | +-+-+ | mariadb-bin.000001 | 8217 | | mariadb-bin.000002 | +- -+-+ 2 rows in set (0.00 sec) master server write: MariaDB [(none)] > create database wangdb Query OK, 1 row affected (0.00 sec) MariaDB [(none)] > show master logs +-+-+ | Log_name | File_size | +-+-+ | mariadb-bin.000001 | 8217 | mariadb-bin.000002 | 332 | +- -+-+ 2 rows in set (0.00 sec) MariaDB [(none)] > show slave status\ gateway * 1. Row * * Slave_IO_State: Waiting for master to send event Master _ Host: 192.168.27.7 Master_User: repluser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mariadb-bin.000002 Read_Master_Log_Pos: 332 Relay_Log_File: mariadb-relay-bin.000003 Relay_Log_Pos: 618 Relay_Master_Log _ File: mariadb-bin.000002 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: 332 Relay_Log_Space: 9153 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: 11 row in set (0.00 sec)
From the server:
Instant synchronization from the server: MariaDB [(none)] > show databases +-+ | Database | +-+ | information_schema | | hellodb | | mysql | | performance_schema | | test | | wangdb | +-+ 6 rows in set (0.00 sec) stop slave server And update operations on the master server: MariaDB [(none)] > create database wangdb2;Query OK, 1 row affected (0.00 sec) MariaDB [(none)] > create database wangdb3;Query OK, 1 row affected (0.00 sec) slave server; [root@centos7x ~] $systemctl stop mariadb.service [root@centos7x ~] $[root@centos7x ~] $[root@centos7x ~] $systemctl start mariadb.service unknown [(none)] > show databases;No connection. Trying to reconnect...Connection id: 4Current database: * NONE * * +-+ | Database | +-+ | information_schema | | hellodb | | mysql | | performance_schema | | test | | wangdb | | wangdb2 | | wangdb3 | | +-+ 8 rows in set (0.00 sec) record [root@centos7x ~] $ls / var/lib/mysql/aria_log.00000001 ibdata1 mariadb-relay-bin.000004 master.info performance_schema wangdbaria_log_control ib_logfile0 mariadb-relay-bin.000005 mysql relay-log.info wangdb2hellodb ib from the relay-log.info on the server | _ logfile1 mariadb-relay-bin.index mysql.sock test wangdb3 [root@centos7x ~] $[root@centos7x ~] $cat / var/lib/mysql/relay-log.info. / mariadb-relay-bin.000005709mariadb-bin.0000025108 [root@centos7x ~] $cat / var/lib/mysql/master.info 18mariadb-bin.000002510192.168.27.7replusercentos330660001800.0000 master server MariaDB [(none)] > show master logs +-+-+ | Log_name | File_size | +-+-+ | mariadb-bin.000001 | 8217 | | mariadb-bin.000002 | +- -+-+ 2 rows in set (0.00 sec)
