People always ask me if I can separate reading and writing. Sometimes I really don’t know how to answer. Let’s put it this way. The technology itself is not difficult for you. The difficult thing is that we can You can't encounter such a big project. If there is such a big project, the separation of reading and writing is definitely not done by one or two people, but should be the collaboration of many people.
Related learning recommendations: mysql video tutorial
So! I haven't done it.
But...
Can't we use it as an experimental environment? (An awkward smile)
It took me a total of 3 days from looking for documentation to implementing the experiment (because I didn’t focus on this matter all day long). Basically done, that is, if someone asks me if I know how to do it, I will say that I have already done it and it is not a difficult task.
The ones that operate on the main library are
mysql > show master status; #View master status Library status
mysql > grant replication slave on . to 'slave'@'%ip%' identified by 'password'; # to explanation behind: 'slave' represents which account, @ is the IP of the slave server and by is the password;
systemctl status firewalld # Check the firewall status
firewall-cmd – list-all # View the firewall’s port list
firewall-cmd –permanent –zone=public –add-port=3306/tcp # Release port 3306
firewall- cmd –reload # Restart the firewall
In addition, if you have iptable, you can check it yourself
The main operations on the slave library are
- mysql> stop slave; // Stop replication
- mysql> reset slave; // Reset replication
- mysql> start slave; // Start replication
- mysql> show slave status\G; # View status
Master server 192.168.5.238 centos 7.4
- Slave server 192.168.5.239 centos 7.4
- Use logs for master-slave configuration
# aster live copy configurelog-bin=mysql-bin //Log file name
binlog_format=mixed //There are several options for this log format. Everyone chooses the mixed option. statement/row/mixed, if you want to learn more, you can read the official documentation
server-id = 238 //In order to avoid repeated use, try to use the last digit of the server IP to name it
skip_name_resolve=ON //Write this directly That's it, refer to other people's documents
expire_logs_days = 10 //Set the number of days for log storage. I think you don't need to add this. It doesn't matter if you add it, because in the test environment, if it is officially launched and run, you have to weigh it.
# /etc/init.d/mysql restart // This is not necessarily this command. It depends on how you installed centos. There is also systemctl restart mysql. If you don’t understand this, you can do more research. Linux operation##If the two servers are not copied, the uuid of auto.cnf should be different. If they are copied, it is time to take a look. If there is this in the /var and directory of the mysql installation directory Just delete this file or mv back it up
Configuration from the library# mysql -uroot -p "password" //Log in to the mysql server, you will be asked to enter your password
mysql>GRANT replication slave ON . TO 'slave'@'%' IDENTIFIED BY '111111'; // Explain that replication assigns replication permissions. This . can operate which library. The 'slave'@'%' at the end means that any host can use slave to copy from the library, or you can specify After the IP is the password ha
mysql> show master status;
—————— ————- ————– —————— ——————-
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
—————— ————- ————– —————— ——————-
| mysql-bin.000010 | 2812 | # Seeing the above table indicates success
log-bin=mysql-bin //Log file name
binlog_format=mixed //Log formatserver-id =239 //Server ID This is explained the same as the main server, using the tail of the IPSet up the slave library
# expire_logs_days = 10 //I commented out this,
# early-plugin-load = "" //This is not used for the time being. It can be configured if it is enabled. This item, master-slave replication is not a key item
relay_log = mysql-relay-bin //This is a replication log from the slave libraryrelay_log_index=relay_log.index //Log index
# /etc/init.d/mysql restart // This is not necessarily this command. It depends on how you installed centos. There is also systemctl restart mysql. If you don’t understand this, you can do more research on linux operations.
# mysql -uroot -p //Enter the terminal (the reason why I write more details is because I am afraid that people who read this article will misunderstand that the operation is incomplete)Close slave
##mysql>stop slave; //This is to close the slave library, make sure you can get through it
Modify master parameters
master_host='192.168.5.238',
master_user='slave',master_password='Fill in the password when setting the master library copy permission above',master_log_file='mysql-bin.000010', //Fill in the file name of the master library show master status here, just copy the location.
master_log_pos=2812; //Look in the show master status of the main library
##Start the slave library
Check whether the master-slave replication is successful
Slave_IO_State: Waiting for master to send event Master_Host: 192.168.5.238 Master_User: slave Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000010 Read_Master_Log_Pos: 2812 Relay_Log_File: mysql-relay-bin.000013 Relay_Log_Pos: 3025 Relay_Master_Log_File: mysql-bin.000010 Slave_IO_Running: Yes Slave_SQL_Running: Yes
Seeing that the values of Slave_IO_Running and Slave_SQL_Running are both Yes, it means it is successful. If it fails, please see the prompt message below and you should be able to find it by searching Google Baidu.
I am here I encountered a problem during configuration, that is, a uuid error was reported. The reason is that my experimental environment was copied from a virtual machine after mysql was installed, so the uuid of mysql was the same
# cd /usr/local/mysql/var
# mv auto.cnf auto.cnf.back //See if this is present. If not, don’t operate. If so, that’s it
# /etc/init.d/mysql restart //Restart the databaseThen execute show slave status\G; observe two yes
So far , the log-based master-slave replication is completed
The above is the detailed content of Detailed explanation of mysql log-based master-slave replication. For more information, please follow other related articles on the PHP Chinese website!