Build Mysql master-slave database

Posted by Trenchant on Sat, 12 Feb 2022 02:10:56 +0100

mysql 5.7 installation

Download yum source

$ vagrant up		# Start the virtual machine
$ vagrant ssh		# Connect to virtual machine
# Download mysql 5.7

Install yum source locally

yum install -y mysql-community-server

Check whether the mysql source is successfully installed

yum repolist enabled | grep "mysql.*-community.*"

The following message appears indicating that the installation was successful:

mysql-connectors-community/x86_64 MySQL Connectors Community                 20
mysql-tools-community/x86_64      MySQL Tools Community                      12
mysql57-community/x86_64          MySQL 5.7 Community Server                 50

Use the yum install command to install Mysql:

$ yum install -y mysql-community-server
already installed:
  mysql-community-libs.x86_64 0:5.7.34-1.el7                                            
  mysql-community-libs-compat.x86_64 0:5.7.34-1.el7                                     
  mysql-community-server.x86_64 0:5.7.34-1.el7                                          

Installed as a dependency:
  mysql-community-client.x86_64 0:5.7.34-1.el7                                          
  mysql-community-common.x86_64 0:5.7.34-1.el7                                          

  mariadb-libs.x86_64 1:5.5.68-1.el7                                                    

# Prompt for successful installation

Start mysql:

systemctl start mysqld

After startup, check the status of Mysql:

systemctl status mysqld

If the above command displays the following information, the installation is successful:

● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: active (running) since II. 2021-05-18 20:50:27 CEST; 2min 18s ago
     Docs: man:mysqld(8)
  Process: 12715 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/ $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
  Process: 12496 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
 Main PID: 12718 (mysqld)
   Memory: 158.3M
   CGroup: /system.slice/mysqld.service
           └─12718 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/

5 June 18-20:50:13 k8s-master systemd[1]: Starting MySQL Server...
5 June 18-20:50:27 k8s-master systemd[1]: Started MySQL Server.

Set startup:

systemctl enable mysqld

Modify MySQL password

After mysql is installed, the default password generated is / var / log / mysqld Log file. Use the grep command to find the password in the log.

 grep 'temporary password' /var/log/mysqld.log
2021-05-18T18:50:21.200923Z 1 [Note] A temporary password is generated for root@localhost: ujTio<m:g5S%

Log in to mysql for the first time and change the password:

mysql -uroot -p
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'Test@123'; 

Allow remote login

Modify root to allow remote connection. It is not recommended for production environment:

mysql> use mysql;
mysql> UPDATE user SET Host='%' WHERE User='root';
mysql> flush privileges;

Open log (not required)

(not required) start the log of sql statement. It is not recommended to start it in the production environment:

View the log directory and open the log of sql statement:

mysql>  show variables like '%general_log%';
mysql>  set global general_log=on;

After startup, restart Mysql, and the above log configuration will become invalid.

Master-slave configuration of Mysql

Machine preparation

Follow the above steps to install mysql5 on the two machines 7. The ip addresses of the two machines are:

  • main
  • from

Create two databases on the two machines, test and test2 respectively, and the character code is utf8:

mysql> CREATE DATABASE `test` CHARACTER SET utf8 COLLATE utf8_general_ci;
Query OK, 1 row affected (2.42 sec)
mysql> CREATE DATABASE `test2` CHARACTER SET utf8 COLLATE utf8_general_ci;
Query OK, 1 row affected (1.64 sec)
mysql> show databases;

Turn off the firewall

To ensure that the 3306 port of the firewall is open, if it is only for learning, you can directly close the firewall.

centos closes the firewall: service iptables stop or systemctl stop firewalld

master node configuration

[root@k8s-master vagrant]# hostname -I 

Configuration on machine

vim /etc/my.cnf

#Server ID is the unique identification of the database service
##Log bin setting this parameter means that binlog function is enabled and the path name is specified
##Sets the number of days the log expires
  • The server ID here is used to identify the unique database. It must be set to different values in the slave database.
  • Binlog ignore DB: indicates the database ignored during synchronization
  • Binlog do DB: Specifies the database to be synchronized

Restart mysql and the configuration takes effect. Execute the following command: systemctl restart mysqld

After the restart is successful, log in to mysql.

Give the Slave database permission account, allow the user to read the log on the master database, and give, that is, the Slave machine has File permission,
It's not enough to only give the Slave machine File permission, but also give it the permission of REPLICATION SLAVE.

mysql> grant FILE on *.* to 'root'@'' identified by 'Test@124';
mysql> grant replication slave on *.* to 'root'@'' identified by 'Test@124';
mysql> flush privileges;

The users here are the users used from the library during synchronization.

Restart mysql (systemctl restart mysqld), log in to mysql and view the main database information

mysql> show master status;
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB                                | Executed_Gtid_Set |
| mysql-bin.000001 |      154 | test,test2   | information_schema,sys,mysql,performance_schema |                   |
1 row in set (1.67 sec)

Configure slave Library

1. Modify / etc / my cnf


Note: the server IDs of the two slave libraries are different and need to be unique.

2. Finished modifying / etc / my After CNF, restart mysql (systemctl restart mysqld)

Enter the mysql console of Slave and perform the following operations:

mysql>  mysql -uroot -p
mysql> stop slave;
change master to   master_host='',master_user='root',master_password='Test@123',master_log_file='mysql-bin.000001', master_log_pos=154;
mysql> start slave;

Note: the above master_log_file is the file displayed in show master status in Master,
And master_log_pos is the Position displayed in show master status in Master.

When configuring the second slave library, you need to re obtain File and position from matser.
3. Then you can view the configuration information through show slave status.

mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Connecting to master
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 154
               Relay_Log_File: k8s-node1-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Connecting
            Slave_SQL_Running: Yes
              Replicate_Do_DB: test,test2
          Replicate_Ignore_DB: mysql
                   Last_Errno: 0

The message above has Slave_IO_Running: Yes and Slave_SQL_Running: Yes, it proves that the master-slave synchronization is successful.

reference resources

Topics: MySQL