mysql5.7 master slave replication setup

Posted by otuatail on Tue, 22 Feb 2022 06:32:31 +0100

1: Check whether mysql has been installed locally

rpm -qa | grep mysql

2: Uninstall previous mysql

rpm -e Existing MySQL full name

Step 2: unzip the file

[root@MiWiFi-R3-srv ~]# tar -zxvf mysql-5.7.17-linux-glibc2.5-x86_64.tar.gz -C /usr/local/

Change the file name to mysql:

[root@MiWiFi-R3-srv local]# mv mysql-5.7.17-linux-glibc2.5-x86_64/ mysql

Step 3: configure startup file

Then go to the mysql support files directory and copy my CNF to / etc / my CNF (automatically read when mysqld starts)

[root@MiWiFi-R3-srv local]# cd mysql/support-files/
[root@MiWiFi-R3-srv support-files]# cp my-default.cnf /etc/my.cnf
cp: overwrite '/etc/my.cnf'? yes 
  • : if you install the default mysql when installing the Linux virtual machine, and then operate the above steps, the terminal will prompt you whether the file already exists or not. Enter yes to overwrite it.

2. Configure database encoding

[root@MiWiFi-R3-srv support-files]# vim /etc/my.cnf

Add the following:



3. Copy mysql Server to / etc / init D / directory (the purpose is to realize the automatic execution effect of startup)

[root@MiWiFi-R3-srv support-files]# cp mysql.server /etc/init.d/mysql

4. Modify / etc / init D / MySQL parameters

[root@MiWiFi-R3-srv support-files]# vim /etc/init.d/mysql

Modify the following:


5. For security and convenience, create a special user who operates the database

Create a mysql group:

[root@MiWiFi-R3-srv support-files]# groupadd mysql

Create a mysql user and put the user into the mysql group:

[root@MiWiFi-R3-srv support-files]# useradd -r -g mysql mysql

Set a password for mysql user:

[root@MiWiFi-R3-srv support-files]# passwd mysql

Change the owner of the directory / usr/local/mysql:

[root@MiWiFi-R3-srv support-files]# chown -R mysql:mysql /usr/local/mysql/

Step 4: initialize mysql database

[root@MiWiFi-R3-srv support-files]# cd /usr/local/mysql/bin/
[root@MiWiFi-R3-srv bin]# ./mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data

A temporary password is generated after initialization root@localhost :: * (it's best to record this temporary password first)

2. Encrypt the database

[root@MiWiFi-R3-srv bin]# ./mysql_ssl_rsa_setup --datadir=/usr/local/mysql/data

3. Start mysql

[root@MiWiFi-R3-srv bin]# ./mysqld_safe --user=mysql &

4. Check whether mysql is started

[root@MiWiFi-R3-srv bin]# ps -ef|grep mysql

If a process is found, it means that the startup is successful.

Step 5: enter the client

1. Login:

 [root@MiWiFi-R3-srv bin]# ./mysql -uroot -p
Enter password:Enter the previous temporary password here

2. Change password

mysql> set password=password('New password');

Step 6: set up remote access

1: Open the default port 3306 of mysql:

[root@MiWiFi-R3-srv bin]# firewall-cmd --zone=public --add-port=3306/tcp --permanent
[root@MiWiFi-R3-srv bin]# firewall-cmd --reload

2: Set up remote access to mysql

Set remote access account: grant all privileges on To remote access user name @ '%' identified by 'user password';

mysql> grant all privileges on *.* to root@'%' identified by 'root';


mysql> flush privileges;

Step 7: set the startup self startup

1. Add service mysql

[root@MiWiFi-R3-srv bin]# chkconfig --add mysql

2. Set mysql service to self start

[root@MiWiFi-R3-srv bin]# chkconfig mysql on

Step 8: configure environment variables

[root@MiWiFi-R3-srv ~]# vim /etc/profile

Add to the last line:

export PATH=$JAVA_HOME/bin:/usr/local/mysql/bin:$PATH

Make the modification effective:

[root@MiWiFi-R3-srv ~]# source /etc/profile

After mysql is set up, start master-slave replication

1. Set up the host:

vim /etc/my.cnf
#Primary server unique ID
#Enable binary logging
#Set the database not to be copied (multiple databases can be set)
#Set the database to be replicated
Binlog do DB = name of the primary database to be replicated
#Format logbin

2. Set up slave

Modify the configuration file: VIM / etc / my cnf
#Unique ID of slave server
#Enable relay logging

3. Restart MySQL service from host and slave

4. Establish an account on the host and authorize the slave



Query the status of master
show master status;
#Record the values of File and Position
#After this step, do not operate the master server MySQL again to prevent the state value of the master server from changing


Configure the host to be replicated on the slave

CHANGE MASTER TO MASTER_HOST = 'IP address of host',
MASTER_LOG_FILE='mysql-bin. Specific number ', MASTER_LOG_POS = specific value;


Start copy from server
start slave;
#View slave server status
show slave status\G;

If the following two parameters are Yes, the master-slave configuration is successful!
# Slave_IO_Running: Yes
# Slave_SQL_Running: Yes

8. Test: create a new database in the master database, create a new table, insert data, and then log in to the slave server to check whether the data exists.

Topics: Database MySQL