centos7 mariadb master slave replication configuration setup detail steps
- 2020-05-17 07:31:41
- OfStack
It took 1 day to implement the centos7 mariadb master and slave replication configuration setup
Environment:
The virtual machine: vm8; centos7 version: 7.2.1511; mariadb version: centos7.2 built-in
Main library server: 10.69.5.200, CentOS 7, MariaDB 10 installed, data available.
Server 1:10.69.5.201, CentOS 7, MariaDB 10 installed, no application data.
Primary server configuration
The following operations are performed on /etc/ my.cnf on the primary server 192.168.71.151.
1. Modify the configuration file, command: vim /etc/ my.cnf, enter the following code:
[root@localhost ~]# cat /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
`# The newly added part
# The following information needs to be added when configuring master and slave start
innodb_file_per_table=NO
log-bin=/var/lib/mysql/master-bin #log-bin If no storage directory is specified, it is the default datadir Directory to point to
binlog_format=mixed
server-id=200
# Each server needs to be added server_id Configuration for each server server_id Need assurance 1 Sex, usually set to server in practice IP End of address 1 position
# The following information needs to be added when configuring master and slave end
`
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-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
[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid
#
# include all files from the config directory
#
!includedir /etc/my.cnf.d
Finally, : wq! Save the exit
2. Restart the mariadb service and enter the command
[root@localhost ~]# systemctl restart mariadb.service
3. The login mariadb
[root@localhost ~]# mysql -u root -padmin
Note: after -p is the password, there is no space in the middle
4. Create an account and give replication permission
This account is used when copying data from the master
MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO 'root'@'10.69.5.%' IDENTIFIED BY 'admin';
Query OK, 0 rows affected (0.00 sec)
5. Back up the database data for import into the slave database
lock
In practice, you're not allowed to write data to the library when you're backing up, so the database is locked and can only be read
MariaDB [(none)]> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.00 sec)Record the master library log file and its current location
MariaDB [(none)]> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 694 | | |
+------------------+----------+--------------+------------------+Remember that the File and Position sections are used by the slave server
Backup the data and enter the command:
[root@localhost ~]# mysqldump -uroot -p --all-databases > /root/db.sqlUnlock the master library
Once the data backup is complete, the lock on the main library can be released:
MariaDB [(none)]> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)Slave server configuration
The following actions are on the slave server
1. Import the data of the main library
[root@localhost ~]# mysql -uroot -p < db.sql
2. From the server /etc/ my.cnf configuration, set relay-log
Add 1 line relay_log= relay-bin to the my.cnf file
If not set, the default is to generate relay log by hostname + "-relay-bin".
[root@localhost ~]# cat /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
`# The following information needs to be added when configuring master and slave start
innodb_file_per_table=NO
server-id=201 #1 General and server ip Final figure 1 to
relay-log=/var/lib/mysql/relay-bin
# The following information needs to be added when configuring master and slave end
`
# 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
[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid
#
# include all files from the config directory
#
!includedir /etc/my.cnf.d
3. Restart the service
[root@localhost ~]# systemctl restart mariadb.service
4. Login mariadb
[root@localhost ~]# mysql -u root -padmin
5. Set master-slave replication
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='10.69.5.200',MASTER_USER='root', MASTER_PASSWORD='admin', MASTER_LOG_FILE='master-bin.000001', MASTER_LOG_POS= 694;
Query OK, 0 rows affected (0.02 sec)This command does the following:
a. Sets the slave library of the current server for the master server (10.69.5.200)
Provide the username and password required for the current database (slave) to copy data from the master, GRANT REPLICATION SLAVE ON *.* TO 'root'@'10.69.5.%' IDENTIFIED BY 'admin' above; Set up the
c. Specify the log file and file location to use when copying from the primary library, that is, SHOW MASTER STATUS is executed by the primary library above. Show File and Position in the results
6. Enable master-slave replication
[root@localhost ~]# systemctl restart mariadb.service
7. View slave status
[root@localhost ~]# systemctl restart mariadb.serviceNote: Slave_IO_Running and Slave_SQL_Running in the results must be Yes, if not, you need to modify it according to the error prompted.
validation
Master server:
[root@localhost ~]# systemctl restart mariadb.serviceSee if the slave server data has changed:
[root@localhost ~]# systemctl restart mariadb.serviceAs you can see, the data has been updated from the server
Problems encountered during setup and solutions
Problem 1: there is an error setting master slave replication from the slave server:
MariaDB [mytest]> start slave;
ERROR 1201 (HY000): Could not initialize master info structure; more error messages can be found in the MariaDB error logfound
Slave_IO_Running: No
Slave_SQL_Running: NoStep 1: what I typed is: CHANGE MASTER TO MASTER_HOST='192.168.71.151',MASTER_USER='slave_user', MASTER_PASSWORD='bigs3cret', MASTER_LOG_FILE=' mysql-bin.000001 ', MASTER_LOG_POS = 469;
Reenter: MariaDB [(none)]
>
CHANGE MASTER TO MASTER_HOST='10.69.5.200',MASTER_USER='root', MASTER_PASSWORD='admin', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS= 469;
Error reporting: ERROR 1201 (HY000): Could not initialize master info structure; more error messages can be in the MariaDB error log
Then see the error log: / var/log/mariadb/mariadb log
The location of the error log is configured in /etc/ my.cnf: log-error =/
[root@localhost ~]# systemctl restart mariadb.service
By looking for the answer: delete/var/lib/mysql/path the 'master. info' 'mysqld - relay - bin. *' 'relay - log. info' 'relay - log - index. *'
Run the command: rm-rf master.info, rm-rf *relay*
Restart service: [root @localhost mysql]# systemctl restart mariadb.service
Enter the mariadb:
[root@localhost mysql]# mysql -u root -padmin
MariaDB [(none)]> flush logs;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> reset slave;
Query OK, 0 rows affected (0.00 sec)
Reset the master-slave replication relationship:
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='10.69.5.200',MASTER_USER='root', MASTER_PASSWORD='admin', MASTER_LOG_FILE='master-bin.000001', MASTER_LOG_POS= 694;
Query OK, 0 rows affected (0.02 sec)
This time it worked.
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.01 sec)
View the status of the slave:
MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Connecting to master
Master_Host: 10.69.5.200
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000001
Read_Master_Log_Pos: 694
Relay_Log_File: relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: master-bin.000001
Slave_IO_Running: Connecting
Slave_SQL_Running: Yes
・ ・ ・
・ ・ ・
・ ・ ・
Replicate_Ignore_Server_Ids:
Master_Server_Id: 0
1 row in set (0.00 sec)
2.Slave_IO_Running: Connecting
Q.2 Slave_IO_Running: Connecting
View the error log
[root@localhost ~]# cat /var/log/mariadb/mariadb.log
・ ・ ・
160915 13:17:56 [Note] Slave SQL thread initialized, starting replication in log 'master-bin.000001' at position 694, relay log '/var/lib/mysql/relay-bin.000001' position: 4
160915 13:17:56 [ERROR] Slave I/O: error connecting to master 'root@10.69.5.200:3306' - retry-time: 60 retries: 86400 message: Can't connect to MySQL server on '10.69.5.200' (113), Error_code: 2003
At this point, run the telnet command
[root@localhost ~]# telnet 10.69.5.200 3306-bash: telnet: command not found
Install telnet
[root@localhost ~]# yum -y install telnet-server.x86_64
Restart the telnet service after successful installation
[root@localhost ~]# systemctl start telnet.socket
[root@localhost ~]# systemctl enable telnet.socket
[root@localhost ~]# telnet 10.69.5.200 3306
-bash: telnet: command not found
Still won't do
This time I restart the virtual machine with reboot and run the command
Note: instead of "yum-y telnet server. x86_64", there is no telnet-server
[root@localhost ~]# yum install telnet.x86_64
It worked
Then,
[root@localhost ~]# systemctl enable telnet.socket
[root@localhost ~]# systemctl start telnet.socket
[root@localhost ~]# firewall-cmd --add-service=telnet --permanent
success
[root@localhost ~]# telnet
telnet>
telnet was finally installed
Starting with the latest version of centos7, the default is Mariadb instead of mysql!
Using the built-in repos installation is simple:
yum install mariadb mariadb-server
The end!