Environmental Science:
192.168.205.37: as master server 192.168.205.47: as slave server
Edition:
OS: centos 7 1810 with mini install mysql-5.7.26-el7-x86_64.tar.gz
Purpose:
GTID(global transaction ID) global transaction identifier, MySQL 5.6 version began to support, GTID replication, unlike traditional replication (asynchronous delayed replication, semi-synchronous replication) needs to find the binlog and pos point, only need to know the master's IP, port, account, password can be, after opening GDIT, change master to master_auto_position=1 can be executed, it will automatically find synchronization. We use MySQL 5.7 to open two server s, one for the master and the other for the slave, to test the GDIT function.
Steps:
- Unzip files
[root@centos7 mysql]#tar xvf mysql-5.7.26-el7-x86_64.tar.gz -C /usr/local/
- Creating Soft Links
[root@centos7 local]#ln -s mysql-5.7.26-el7-x86_64/ mysql
- Add mysql account
[root@centos7 local]#useradd -r -s /bin/false mysql
- Preparing environmental variables
[root@centos7 mysql]#echo 'PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh [root@centos7 mysql]#echo $PATH
- Prepare a data catalog (you can not build it, it will be created automatically when you initialize the database)
[root@centos7 mysql]#mkdir /data/mysql [root@centos7 mysql]#chown mysql:mysql /data/mysql
- Install related packages (as required, prompted when initialized)
[root@centos7 mysql]#yum install libaio
- Preliminary database
[root@centos7 mysql]#mysqld --initialize --user=mysql --datadir=/data/mysql 2019-08-12T00:43:03.799485Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details). 2019-08-12T00:43:04.007086Z 0 [Warning] InnoDB: New log files created, LSN=45790 2019-08-12T00:43:04.043130Z 0 [Warning] InnoDB: Creating foreign key constraint system tables. 2019-08-12T00:43:04.100702Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 24ddb90b-bc9a-11e9-856e-000c2956e1ea. 2019-08-12T00:43:04.101693Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened. 2019-08-12T00:43:04.102159Z 1 [Note] A temporary password is generated for root@localhost: Ia-ClrMga7L/
- Modify the database configuration file
[root@centos7 mysql]#cp -b /etc/my.cnf{,.bak} [root@centos7 mysql]#rpm -qf /etc/my.cnf mariadb-libs-5.5.60-1.el7_5.x86_64 [root@centos7 mysql]#vi /etc/my.cnf [mysqld] datadir=/data/mysql socket=/data/mysql/mysql.sock log-error=/data/mysql/mysql.log pid-file=/data/mysql/mysql.pid [client] socket=/data/mysql/mysql.sock - Prepare startup scripts
[root@centos7 mysql]#cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld [root@centos7 mysql]#chkconfig --list [root@centos7 mysql]#chkconfig --add mysqld [root@centos7 mysql]#chkconfig --list [root@centos7 mysql]#service mysqld start Starting MySQL.Logging to '/data/mysql/mysql.log'. SUCCESS!
- Change Password
[root@centos7 mysql]#mysql -p"Ia-ClrMga7L/" [root@centos7 mysql]#mysqladmin -uroot -p"Ia-ClrMga7L/" password centos [root@centos7 mysql]#mysql -pcentos
- The table stored in the user account of the database has changed, without password, and is replaced by authentication_string.
mysql> desc user; mysql> select user,host,authentication_string from user; +---------------+-----------+-------------------------------------------+ | user | host | authentication_string | +---------------+-----------+-------------------------------------------+ | root | localhost | *128977E278358FF80A246B5046F51043A2B1FCED | | mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | | mysql.sys | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | +---------------+-----------+-------------------------------------------+ 3 rows in set (0.00 sec)
- Edit the configuration file to set up the main server and turn on the GTID function
[root@centos7 ~]#vi /etc/my.cnf [mysqld] server-id=37 log-bin gtid_mode=ON enforce_gtid_consistency datadir=/data/mysql socket=/data/mysql/mysql.sock log-error=/data/mysql/mysql.log pid-file=/data/mysql/mysql.pid [client] socket=/data/mysql/mysql.sock [root@centos7 ~]#service mysqld restart Shutting down MySQL. SUCCESS! Starting MySQL. SUCCESS!
- Create duplicate account
mysql> grant replication slave on *.* to repluser@'192.168.205.%' identified by 'centos'; Query OK, 0 rows affected, 1 warning (0.00 sec)
- Setting up configuration files on slave servers
[root@centos7 ~]#vi /etc/my.cnf [mysqld] server-id=47 gtid_mode=ON enforce_gtid_consistency datadir=/data/mysql socket=/data/mysql/mysql.sock log-error=/data/mysql/mysql.log pid-file=/data/mysql/mysql.pid [client] socket=/data/mysql/mysql.sock [root@centos7 ~]#service mysqld restart Shutting down MySQL.. SUCCESS! Starting MySQL. SUCCESS!
- Log in from the server and modify the change master to without referring to location and file name
[root@centos7 ~]#mysql -pcentos mysql> CHANGE MASTER TO -> MASTER_HOST='192.168.205.37', -> MASTER_USER='repluser', -> MASTER_PASSWORD='centos', -> MASTER_PORT=3306, -> MASTER_AUTO_POSITION=1; Query OK, 0 rows affected, 2 warnings (0.01 sec) - View replication status
mysql> start slave; Query OK, 0 rows affected (0.01 sec) mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.205.37 Master_User: repluser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: centos7-bin.000005 Read_Master_Log_Pos: 453 Relay_Log_File: centos7-relay-bin.000002 Relay_Log_Pos: 670 Relay_Master_Log_File: centos7-bin.000005 Slave_IO_Running: Yes Slave_SQL_Running: Yes - Test database replication
mysql> create database db1; Query OK, 1 row affected (0.01 sec)
- View the replication status from the server and the replication is successful
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | db1 | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.00 sec)