1. Install mysql on the host, it is convenient to wait a while for the master and slave to build on the slave and then install mysql. You can refer to the following links:
Detailed steps to install MySQL on Centos7_Installing mysql on centos7
2. Clone the host centos7 (master). Clone a slave machine.
Little by little all the way if really not. Refer to the following link (including installation and cloning of centos7):
CentOS7 installation and cloning_centos cloning_Ran Banxiasheng’s Blog-CSDN Blog
3. Here, the network card of the cloned virtual machine must be modified. Otherwise, it is the same as the host’s ip, and it will conflict if opened at the same time. The following is the network card configuration. The network card configuration of the slave machine must comment out the uuid.
[Configuration] How to configure the network card in centos7_centos7 network card configuration
4. When building mysql as the master and slave, because the service-uuid of the cloned mysql is the same, it must be changed here, just change one of the master and slave at random.
find / -iname "auto.cnf" /var/lib/mysql/auto.cnf #Modify the value of service-uuid from the searched result path [auto] server-uuid=312c2259-f54f-11ed-8a9a-000c29131471 vim /var/lib/mysql/auto.cnf
5. Master-slave configuration
5.1 Host configuration mysql configuration file【my.cnf】|【my.ini】
The master is as follows:
[mysqld] server-id = 1 # node ID, ensure unique log config log-bin = mysql-bin #Enable mysql's binlog log function, binlog log location sync_binlog = 1 #Control the binlog of the database to be flushed to the disk, 0 is not controlled, the performance is the best, 1 will be flushed to the log file every time a transaction is submitted, the performance is the worst and the safest binlog_format = mixed #binlog log format, mysql uses statement by default, it is recommended to use mixed expire_logs_days = 7 #binlog expiration cleanup time max_binlog_size = 100m #binlog each log file size binlog_cache_size = 4m #binlog cache size binlog-do-db=crm #The database that needs to be synchronized binlog-do-db=test #The database that needs to be synchronized max_binlog_cache_size= 512m #The maximum binlog cache is large binlog-ignore-db=mysql #Databases that do not generate log files, multiple ignore databases can be spliced with commas, or copy this sentence and write multiple lines auto-increment-offset = 1 # auto-increment offset auto-increment-increment = 1 # Auto-increment of auto-increment slave-skip-errors = all #skip slave library errors
slave configuration:
[mysqld] server-id=2 log-bin=mysql-bin #If you do not need to synchronize to other databases from the database, you can comment it out relay-log=slave-relay-bin #Must be enabled, the binlog synchronized from the master database will be written to this directory relay-log-index=slave-relay-bin #If the name of the master-slave database is the same (multiple can be configured) replication-do-db=crm #If the names of the master and slave databases are different (multiple can be configured) replication-rewrite-db= master database name -> slave database name
5.2 Restart of master and slave mysql services
service mysql restart
5.3 Create a remote user and configure it in the host mysql.
Suppose the master’s ip is 192.168.2.87 and the slave’s ip is: 192.168.2.88.
CREATE USER slave IDENTIFIED BY 'Slave@123'; # The password is too simple to change the password # The default is 1, which is MEDIUM, so the password set at the beginning must meet the length, and must contain numbers, lowercase or uppercase letters, and special characters set global validate_password_policy=0; # close password complexity policy set global validate_password_length=1; # Set password complexity and require a password with a minimum length of 1 select @@validate_password_policy; # View password complexity policy select @@validate_password_length; # View password complexity requires minimum password length Set the mysql password policy and length according to your own needs #The following ip is the ip of the slave machine, which means which remote ip can call the master mysql of the host grant replication SLAVE on *.* to 'slave '@'192.168.2.88' identified by 'Slave@123'; # Refresh permissions don't forget flush privileges;
5.4 View the master information on the master server, you need to remember [file] and [position]
mysql> show master status; + ------------------ + ---------- + -------------- + ---- -------------- + ------------------- + | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | + ------------------ + ---------- + -------------- + ---- -------------- + ------------------- + | mysql-bin.000002 | 665 | crm | mysql | | + ------------------ + ---------- + -------------- + ---- -------------- + ------------------- + 1 row in set (0.00 sec)
5.5 Log in to the slave server mysql, and add the master information that the slave node needs to synchronize, among which [master_log_file] and [master_log_pos] are two field information obtained from the previous step. Execute the following statement
change master to master_host='192.168.2.87', master_user='slave', master_password='Slave@123', master_log_file='mysql_bin.000002', master_log_pos=665; Parameter explanation: MASTER_HOST : Set the ip address of the master server to be connected MASTER_USER : Set the username of the master server to connect to MASTER_PASSWORD : Set the password of the master server to connect to MASTER_LOG_FILE : Set the log name of the bin log of the master server to be connected, that is, the information obtained in 5.4 MASTER_LOG_POS : Set the recording position of the bin log of the master server to be connected, that is, the information obtained in 5.4, (note here that the last item does not need to be quoted. Otherwise, the configuration fails)
5.6 Start the master-slave on the slave server
stop slave; reset slave;
5.7 Check the master-slave synchronization status, no error is reported and the startup is successful
show slave status \G;
OR
show slave status;
*************************** 1. row ********************* *****
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.2.87
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 154
Relay_Log_File: localhost-relay-bin.000010
Relay_Log_Pos: 367
Relay_Master_Log_File: mysql-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: 154
Relay_Log_Space: 744
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: 1
Master_UUID: 312c2259-f54f-11ed-8a9a-000c29131471
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
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
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
5.8 See that the following are yes, the master-slave is set up.
Slave_IO_Running: Yes Slave_SQL_Running: Yes
6 some errors
1. If the remote connection fails, it may be that the port of the master host is not opened. 1. Open port 3306 firewall-cmd --zone=public --add-port=3306/tcp --permanent 2. Restart the firewall firewall-cmd --reload 3. View the ports that have been opened firewall-cmd --list-ports 2. Turn off the firewall directly Start: systemctl start firewalld Shut down: systemctl stop firewalld View status: systemctl status firewalld Boot disabled: systemctl disable firewalld Boot enable: systemctl enable firewalld 3. When cloning from a slave, sometimes the same situation as a mac will appear. I got a gateway error after restarting the slave. I heard from the Internet that it is because my centos7 has installed a desktop with a NetworkManager, which affects the work of the network. I solved the problem by closing NetworkManager directly. Then restart the network card just fine