Shulou(Shulou.com)06/01 Report--

The following brings you the experimental flow and results of realizing GTID function by MySQL5.7, hoping to bring some help to you in practical application.

MYSQL involves a lot of things, and there are not many theories. There are many books on the Internet. Today, we will use the accumulated experience in the industry to do an answer.

192.168.205.37: as master server 192.168.205.47: as slave server OS: centos 7 1810 with mini install mysql-5.7.26-el7-x86_64.tar.gz

GTID (global transaction ID) global transaction identifier is supported by the mysql5.6 version. Unlike traditional replication methods (asynchronous replication and semi-synchronous replication), GTID replication needs to find binlog and pos points. You only need to know the IP, port, account number and password of master. When GDIT is enabled, execute change master to master_auto_position=1, and it will automatically find synchronization. We use mysql5.7 to open two server, one master and one slave, to test the GDIT function.

Unzip file [root@centos7 mysql] # tar xvf mysql-5.7.26-el7-x86_64.tar.gz-C / usr/local/ create soft link [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 prepare environment variable [root@centos7 mysql] # echo 'PATH=/usr/local/mysql/bin:$PATH' > / Etc/profile.d/mysql.sh [root@centos7 mysql] # echo $PATH prepares the data directory (you don't have to build it It is automatically created when initializing the database) [root@centos7 mysql] # mkdir / data/mysql [root@centos7 mysql] # chown mysql:mysql / data/mysql installation package (prompted when initializing) [root@centos7 mysql] # yum install libaio initialization database [root@centos7 mysql] # mysqld-- initialize-- user=mysql-- datadir=/data/mysql2019-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=457902019-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 database configuration file [root@centos7 mysql] # cp-b / etc/my.cnf { .bak} [root@centos7 mysql] # rpm-qf / etc/my.cnfmariadb-libs-5.5.60-1.el7_5.x86_64 [root@centos7 mysql] # vi / etc/ my.cnf [mysqld] datadir=/data/mysqlsocket=/data/mysql/mysql.sock log-error= / data/mysql/mysql.logpid-file=/data/mysql/mysql.pid [client] socket=/data/mysql/mysql.sock prepares to launch script [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 startStarting MySQL.Logging to'/ data/mysql/mysql.log'. SUCCESS! Change the password [root@centos7 mysql] # mysql-p "Ia-ClrMga7L/" [root@centos7 mysql] # mysqladmin-uroot-p "Ia-ClrMga7L/" password centos [root@centos7 mysql] # mysql-pcentos database the table stored in the user account has changed, without password, use authentication_string instead of 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 configuration file to set up the primary CVM Enable the GTID function [root@centos7 ~] # vi / etc/my.cnf [mysqld] server-id=37log-bin gtid_mode=ONenforce_gtid_consistencydatadir=/data/mysqlsocket=/data/mysql/mysql.socklog-error=/data/mysql/mysql. Logpid-file=/data/mysql/mysql.pid [client] socket=/data/mysql/mysql.sock [root@centos7 ~] # service mysqld restartShutting down MySQL. SUCCESS! Starting MySQL. SUCCESS! Create a replication account mysql > grant replication slave on *. * to repluser@'192.168.205.%' identified by 'centos' Query OK, 0 rows affected 1 warning (0.00 sec) set the configuration file [root@centos7 ~] # vi / etc/my.cnf [mysqld] server-id=47gtid_mode=ONenforce_gtid_consistencydatadir=/data/mysqlsocket=/data/mysql/mysql.socklog-error=/data/mysql/mysql.logpid-file=/data/mysql/mysql.pid on the slave server [client] socket=/data/mysql/mysql.sock [root@centos7 ~] # service mysqld restartShutting down MySQL.. SUCCESS! Starting MySQL. SUCCESS! Log in from the server and modify the change master to without pointing to the location and file name [root@centos7 ~] # mysql-pcentosmysql > 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) to view the replication status mysql > start slave Query OK 0 rows affected mysql > show slave status\ row * 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: replication of Yes test database mysql > create database db1 Query OK, 1 row affected (0.01sec) to check the replication status from the server, replication succeeded mysql > show databases +-+ | Database | +-+ | information_schema | | db1 | | mysql | | performance_schema | | sys | +-+ 5 rows in set (0.00 sec read the above about MySQL5 .7 the experimental flow and results of realizing GTID function If there is anything else you need to know, you can find what you are interested in in the industry information or find our professional technical engineer to answer, the technical engineer has more than ten years of experience in the industry.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.