Some time ago, someone asked me how to build Mysql's active/standby mode, I spent 30 minutes to demonstrate it on the spot, this article provides the whole process of mysql installation and deployment of master+salve mode, mainly demonstrating the operation process of 2 nodes, and the operation mode of multiple nodes is the same. I won't talk about the theoretical knowledge, and I hope it will be helpful to you.
1. Preparation for installation
1.Prepare 2 centos 7.0 operating systems, requiring IP and port intercommunication2.Prepare mysql-5.7.28-linux-glibc2.12-x86_64.tar.gz installation package3.Prepare libaio-0.3.109-13.el7.x86_64 installation packageInstallation steps4.Root user, be sure to remember to use the root user to install, to avoid other problems that are difficult to solve in the future
5. Basic experience in Linux operation
2. MySql installation steps to install libaio, mysql depends on libaio, so you need to ensure that the system has installed libaio before installing mysql (1) Check whether libaio has been installed, it is a good habit to check before installation. If it is not installed, you need to install it by referring to Step 2.
rpm -qa | grep libaio
libaio-0.3.109-13.el7.x86_64
The output is as above, indicating that the server has libaio installed, and (2) has installed libaio
sudo rpm -ivh libaio-0.3.109-13.el7.x86_64.rpm
Verify the installation:
rpm -qa | grep libaio
(3) To install MySQL to create MySQL users and groups, and to set the permissions of the directory, see Create a User Group
sudo groupadd mysql
, skip this step if you have already created one. Create a user
sudo useradd -r -g mysql mysql
Set up users
sudo chown -R mysql:mysql /home/admin/local/mysql5.7
Set the directory read/write permissions
sudo chmod 777 -R /home/admin/local/mysql5.7
初始化mysql--basedir参数:mysql主目录--datadir参数:mysql数据目录
sudo /home/admin/local//mysql5.7/bin/mysqld --initialize-insecure --user=mysql --basedir=/home/admin/local//mysql5.7 --datadir=/home/admin/local//mysql5.7/data
If there is a password-related message in the log, you need to remember the password, and then you need to log in to MySQL.
Copy the mysql.server file
sudo cp /home/admin/local/mysql5.7/support-files/mysql.server /etc/init.d/mysqld
Modify the configuration file
Edit the file /etc/my.cnf, if you don't have my.cnf, you can create one by yourself, the specific configuration content is as shown below.
Create a log file, and set the directory permissions again to create mysql.log file
sudo touch /home/admin/local/mysql5.7/logs/mysql.log
Set user directory permissions
sudo chown -R mysql:mysql /home/admin/local/mysql5.7
Set read and write permissions
sudo chmod 777 -R /home/admin/local/mysql5.7
Set the environment variables, edit the file /etc/profile, and add the following line at the end of the file:
export PATH=$PATH:/home/admin/local/mysql5.7/bin
执行source /etc/profile,让配置生效
Start mysql with the start-stop command
sudo service mysqld start
View the startup status
sudo service mysqld status
If the following information is output, MySQL is running normally.
SUCCESS! MySQL running (18601)
At this point, the Mysql of one of the servers has been installed, and the other server has installed MySQL according to the above steps. Next, start the configuration of primary/standby mode
3. Active/standby mode settings: 1. Select one of the servers as the master node and modify the Mysql configuration file (1) Modify the configuration file /etc/my.cnf
[mysqld]server-id=1log-bin=mysql-bin
'server-id' is the unique ID of the primary server, and 'log-bin' indicates that binary logging is enabled to restart mysql
sudo service mysqld restart
(2) Create a data synchronization user
Log in to the database
mysql -u root -p
Create a data synchronization user
'repl' is the connection username and 'repl123' is the database password (3) to restart mysql
sudo service mysqld restart
(4) Check whether the master is valid and logged in to the database
mysql -u root -p
Remember File and Position, which will be used in the slave server settings. 2. Salve node configuration (1) Modify the configuration file /etc/my.cnf and restart mysql
[mysqld]server-id=2log_bin=mysql-binrelay_log=relay-bin
'server-id' is the unique ID from the server, which needs to be different from master. (2) Set the operation after switching from the server to the slave server. Log in to the mysql -u root -p database
mysql>CHANGE MASTER TOMASTER_HOST='192.168.1.11',MASTER_USER='repl',MASTER_PASSWORD='repl123',MASTER_LOG_FILE='mysql-bin.000002',MASTER_LOG_POS=154;
where 'mysql-bin.000002' is the File in the output result of the master command SHOW MASTER STATUS, MASTER_LOG_POS is the Position, and MASTER_HOST is the IP address of the host. (3) Restart MySQL
sudo service mysqld restart
(4)查看slave同步是否生效登录数据库mysql -u root -p
Slave_IO_Running: Yes and Slave_SQL_Running: Yes indicate that the master-slave replication is in effect. Finally, don't forget to test it, log in to the Mysql database of the master node and create a test database
create database test;
Log in to MySQL on the Slave node,
show databases;
If you see that there is a test database, it has been successful, and if you are not sure, you can create a table and insert data for further testing.