mysql5.7使用gtid模式搭建主从复制架构
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了mysql5.7使用gtid模式搭建主从复制架构，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含12385字，纯文字阅读大概需要18分钟。
内容图文
/]# mkdir /usr/local/src/mysql-5.7.26-src
[root@server-1]# cd /usr/local/src/mysql-5.7.26-src/
[root@server-1 mysql-5.7.26-src]# wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.26.tar.gz
2、添加mysql用户和组
[root@server-1 mysql-5.7.26-src]# useradd mysql [root@server-1 mysql-5.7.26-src]# id mysql uid=1001(mysql) gid=1001(mysql) 组=1001(mysql)
3、设置mysql用户密码
[root@server-1 mysql-5.7.26-src]# passwd mysql 更改用户 mysql 的密码 。 新的 密码： 重新输入新的 密码： passwd：所有的身份验证令牌已经成功更新。
4、新建mysql相关目录，并赋权限给mysql用户
[root@server-1 mysql-5.7.26-src]# mkdir /home/mysql-5.7.26 [root@server-1 mysql-5.7.26-src]# mkdir /home/mysql-5.7.26/data [root@server-1 mysql-5.7.26-src]# mkdir /home/mysql-5.7.26/log [root@server-1 mysql-5.7.26-src]# mkdir -p /home/mysql-5.7.26/run [root@server-1 home]# chown -R mysql:mysql mysql-5.7.26/ [root@server-1 home]# cd mysql-5.7.26/ [root@server-1 mysql-5.7.26]# ll 总用量 0 drwxr-xr-x 2 mysql mysql 6 5月 31 14:25 data drwxr-xr-x 2 mysql mysql 6 5月 31 14:26 log drwxr-xr-x 2 mysql mysql 6 5月 31 14:26 run
5、安装依赖包
[root@server-1]# yum install cmake gcc gcc-c++ ncurses-devel bison zlib libxml openssl automake autoconf make libtool bison-devel libaio-devel -y
6、安装boost
MySQL5.7.24要求boost的版本是1.59，更高版本的不适用MySQL5.7.24
[root@server-1 /]# cd /usr/local/src/ [root@server-1 src]# wget http://downloads.sourceforge.net/project/boost/boost/1.59.0/boost_1_59_0.tar.gz [root@server-1 src]# tar -zxvf boost_1_59_0.tar.gz [root@server-1 src]# mv boost_1_59_0 /usr/local/
7、编译安装
[root@server-1 /]# cd /usr/local/src/mysql-5.7.26-src/ [root@server-1 mysql-5.7.26-src]# tar -zxvf mysql-5.7.26.tar.gz [root@server-1 mysql-5.7.26]# cd mysql-5.7.26
[root@server-1 mysql-5.7.26]# cmake -DCMAKE_INSTALL_PREFIX=/home/mysql-5.7.26 -DINSTALL_DATADIR=/home/mysql-5.7.26/data -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DEXTRA_CHARSETS=all -DWITH_SSL=yes -DWITH_EMBEDDED_SERVER=1 -DENABLED_LOCAL_INFILE=1 -DWITH_MYISAM_STORAGE_ENGINE=1 -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_ARCHIVE_STORAGE_ENGINE=1 -DWITH_BLACKHOLE_STORAGE_ENGINE=1 -DWITH_FEDERATED_STORAGE_ENGINE=1 -DWITH_PARTITION_STORAGE_ENGINE=1 -DMYSQL_UNIX_ADDR=/home/mysql-5.7.26/run/mysql.sock -DMYSQL_TCP_PORT=3306 -DENABLED_LOCAL_INFILE=1 -DSYSCONFDIR=/etc -DWITH_READLINE=on -DWITH_BOOST=/usr/local/boost_1_59_0
如果中途出现错误，则根据错误信息去查找问题，再次编译的时候，一定需要删除mysql-5.7.26下的CMakeCache.txt这个文件，清空缓存才能再次编译，否则还是跟上次一样出现错误。
最后出现
CMake Warning: Manually-specified variables were not used by the project: INSTALL_DATADIR WITH_READLINE -- Build files have been written to: /usr/local/src/mysql-5.7.26-src/mysql-5.7.26
编译成功，再执行
[root@server-1 mysql-5.7.26]# make && make install
最后出现
-- Installing: /home/mysql-5.7.26/mysql-test/lib/My/SafeProcess/Base.pm -- Installing: /home/mysql-5.7.26/support-files/mysqld_multi.server -- Installing: /home/mysql-5.7.26/support-files/mysql-log-rotate -- Installing: /home/mysql-5.7.26/support-files/magic -- Installing: /home/mysql-5.7.26/share/aclocal/mysql.m4 -- Installing: /home/mysql-5.7.26/support-files/mysql.server
8、编写配置文件my.cnf
[root@server-1 mysql-5.7.26]# vim /etc/my.cnf
[mysqld] bsedir=/home/mysql-5.7.26 datadir=/home/mysql-5.7.26/data socket=/home/mysql-5.7.26/run/mysql.sock user=mysql symbolic-links=0 [mysqld_safe] log-error=/home/mysql-5.7.26/log/mysqld.log pid-file=/home/mysql-5.7.26/run/mysqld.pid
9、配置环境变量
[root@server-1 mysql-5.7.26]# vim /etc/profile 在文件末尾加上 PATH=$PATH:/home/mysql-5.7.26/bin/ [root@server-1 mysql-5.7.26]# source /etc/profile
10、初始化系统数据库
[root@server-1 /]# mysqld --initialize --user=mysql --basedir=/home/mysql-5.7.26/ --datadir=/home/mysql-5.7.26/data/ [root@server-1 /]#
没有报错，查看日志中的初始密码
[root@server-1 /]# cat /home/mysql-5.7.26/log/mysqld.log |grep password 2019-06-04T01:56:14.224507Z 1 [Note] A temporary password is generated for root@localhost: dyB8iUq<EaK;
11、启动数据库
[root@server-1 /]# mysqld_safe --defaults-file=/etc/my.cnf --user=mysql & 2019-06-04T02:00:01.566664Z mysqld_safe Logging to ‘/home/mysql-5.7.26/log/mysqld.log‘. 2019-06-04T02:00:01.629556Z mysqld_safe Starting mysqld daemon with databases from /home/mysql-5.7.26/data
12、启动客户端,输入初始密码登录数据库
[root@server-1 ~]# mysql -uroot -p
13、修改密码
mysql> set PASSWORD=PASSWORD(‘xxxxxxx‘); mysql> alter user ‘root‘@‘localhost‘ PASSWORD EXPIRE NEVER; Query OK, 0 rows affected (0.00 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec)
退出，重新登录
[root@server-1 ~]# mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.7.26 Source distribution Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement. mysql>
三、配置主从配置
1、主库172.28.18.69配置文件
[mysqld] basedir=/home/mysql-5.7.26 datadir=/home/mysql-5.7.26/data socket=/home/mysql-5.7.26/run/mysql.sock user=mysql symbolic-links=0 server_id=69 gtid_mode=on #开启gtid模式 enforce_gtid_consistency=on #强制gtid一致性 log-bin=master #开启二进制日志 binlog_format=row [mysqld_safe] log-error=/home/mysql-5.7.26/log/mysqld.log pid-file=/home/mysql-5.7.26/run/mysqld.pid
重启mysql，并登录mysql
[root@server-1 /]# mysqld_safe --defaults-file=/etc/my.cnf --user=mysql & [1] 29747 [root@server-1 /]# 2019-06-04T02:34:07.501598Z mysqld_safe Logging to ‘/home/mysql-5.7.26/log/mysqld.log‘. 2019-06-04T02:34:07.554499Z mysqld_safe Starting mysqld daemon with databases from /home/mysql-5.7.26/data ^C [root@server-1 /]# mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.7.26-log Source distribution Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement. mysql>
查询主库状态
mysql> show master status; +---------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +---------------+----------+--------------+------------------+-------------------+ | master.000001 | 154 | | | | +---------------+----------+--------------+------------------+-------------------+
多了一个Executed_Gtid_Set
查询gitd相关变量
mysql> show variables like ‘%gtid%‘; +----------------------------------+-----------+ | Variable_name | Value | +----------------------------------+-----------+ | binlog_gtid_simple_recovery | ON | | enforce_gtid_consistency | ON | | gtid_executed_compression_period | 1000 | | gtid_mode | ON | | gtid_next | AUTOMATIC | | gtid_owned | | | gtid_purged | | | session_track_gtids | OFF | +----------------------------------+-----------+ 8 rows in set (0.00 sec)
2、主库上创建repl账号
mysql> grant replication slave,replication client on *.* to ‘repl‘@‘%‘ identified by ‘xxxxxx‘; Query OK, 0 rows affected, 1 warning (0.02 sec)
3从库配置文件
[mysqld] basedir=/home/mysql-5.7.26 datadir=/home/mysql-5.7.26/data socket=/home/mysql-5.7.26/run/mysql.sock user=mysql symbolic-links=0 server_id=103 gtid_mode=on #开启gtid模式 enforce_gtid_consistency=on log-bin=salve binlog_format=row [mysqld_safe] log-error=/home/mysql-5.7.26/log/mysqld.log pid-file=/home/mysql-5.7.26/run/mysqld.pid
重启mysql
[root@server-2 log]# mysqld_safe --defaults-file=/etc/mysql/my.cnf & [1] 1037 [root@server-2 log]# 2019-06-04T02:49:07.867996Z mysqld_safe Logging to ‘/home/mysql-5.7.26/log/mysqld.log‘. 2019-06-04T02:49:07.899183Z mysqld_safe Starting mysqld daemon with databases from /home/mysql-5.7.26/data
登录mysql，从库执行以下命令
mysql> change master to master_host=‘172.28.18.69‘ ,master_user=‘repl‘,master_password=‘xxxxxx‘,master_auto_position=1; Query OK, 0 rows affected, 2 warnings (0.30 sec) mysql> start slave; Query OK, 0 rows affected (0.02 sec)
查看从库状态
mysql> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 172.28.18.69 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: master.000001 Read_Master_Log_Pos: 457 Relay_Log_File: server-2-relay-bin.000002 Relay_Log_Pos: 664 Relay_Master_Log_File: master.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 457 Relay_Log_Space: 874 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 69 Master_UUID: ee3e292b-866b-11e9-9df8-14feb5dc2c77 Master_Info_File: /home/mysql-5.7.26/data/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: ee3e292b-866b-11e9-9df8-14feb5dc2c77:1 Executed_Gtid_Set: 520b8b86-8675-11e9-8283-90b11c15be09:1-3, ee3e292b-866b-11e9-9df8-14feb5dc2c77:1 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec)
成功
mysql> mysql> show variables like ‘%gtid%‘; +----------------------------------+----------------------------------------+ | Variable_name | Value | +----------------------------------+----------------------------------------+ | binlog_gtid_simple_recovery | ON | | enforce_gtid_consistency | ON | | gtid_executed_compression_period | 1000 | | gtid_mode | ON | | gtid_next | AUTOMATIC | | gtid_owned | | | gtid_purged | ee3e292b-866b-11e9-9df8-14feb5dc2c77:1 | | session_track_gtids | OFF | +----------------------------------+----------------------------------------+
测试
主库新建test库和test表，插入一条记录
mysql> create database test; Query OK, 1 row affected (0.02 sec) mysql> use test; Database changed mysql> create table test(id int ,name varchar(20)); Query OK, 0 rows affected (0.14 sec) mysql> insert into test values(1,‘aaaaa‘); Query OK, 1 row affected (0.05 sec) mysql> select * from test; +------+-------+ | id | name | +------+-------+ | 1 | aaaaa | +------+-------+ 1 row in set (0.00 sec)
在从库上查看
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | test | +--------------------+ 5 rows in set (0.00 sec) mysql> use test; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> select * from test; +------+-------+ | id | name | +------+-------+ | 1 | aaaaa | +------+-------+ 1 row in set (0.00 sec)
数据同步成功。
mysql5.7使用gtid模式搭建主从复制架构
标签：input RoCE 查看 pid 配置文件 tcp word update man
本文系统来源：https://www.cnblogs.com/sky-cheng/p/10955054.html
内容总结
以上是互联网集市为您收集整理的mysql5.7使用gtid模式搭建主从复制架构全部内容，希望文章能够帮你解决mysql5.7使用gtid模式搭建主从复制架构所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。
来源：【匿名】