快速搭建MYSQL8.0主从关系

海鲨数据库架构师 2022-12-04

228

1. 官方下载yum源

https://dev.mysql.com/downloads/repo/yum/

2. 安装yum源

2.1. 安装前先卸载mysql

# 查看是否存在历史安装

yum list installed mysql*

# 删除安装包

yum remove mysql-community-client    mysql-community-common mysql-community-libs mysql-community-libs-compat mysql-community-server mysql57-community-release

# 删除配置文件

rm -rf var/lib/mysql       
rm /etc/my.cnf

2.1. 安装mysql

rpm -ivhmysql80-community-release-el7-7.noarch.rpm

3 安装mysql8.0.31

yum -y install mysql-community-server

# 启动命令

systemctl start mysqld

4 查看mysql默认初始密码

grep 'temporary password' var/log/mysqld.log
./mysql -uroot -p'<6nC&pSfg9Du'


mysql> alter user 'root'@'localhost' identified by "Shark@666";
mysql> use mysql
mysql> update user set host='%' where host='localhost';
mysql> flush privileges;

5 在主库端创建复制用户

mysql>create user 'Repl'@'%' WITH sha256_password BY 'Shark@888';


mysql>GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'Repl'@'%';

6 安装半同步插件

mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so';
mysql> show variables like '%semi%';
+-------------------------------------------+------------+--------------------------+
| Variable_name                             | Value      | 中文解释 |            
+-------------------------------------------+------------+--------------------------+
| rpl_semi_sync_master_enabled              | OFF        | 开启半同步 |
| rpl_semi_sync_master_timeout              | 10000      | 毫秒 超时 |   
| rpl_semi_sync_master_trace_level          | 32         | 跟踪层次 |
| rpl_semi_sync_master_wait_for_slave_count | 1          | 等待从库数量 | 
| rpl_semi_sync_master_wait_no_slave        | ON         |要等待丛库确认收到 |     
| rpl_semi_sync_master_wait_point           | AFTER_SYNC |到从库前不被其他会话可见 |
+-------------------------------------------+------------+--------------------------+
6 rows in set (0.00 sec)
mysql> exit

7 设置开机自启

systemctl enable mysqld

systemctl status mysqld

systemctl restart mysqld

systemctl daemon-reload # 刷新systemctl配置

8 关闭系统安全

vim  /etc/selinux/config
SELINUX=disabled


chown -R mysql:mysql var/lib/mysql


systemctl disable firewalld
systemctl stop firewalld

9 主库端编辑参数文件

vim etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid


port = 3306
character_set_server=utf8mb4
init_connect='SET NAMES utf8mb4'


log_timestamps = SYSTEM
default-time-zone = '+8:00'
skip-name-resolve


innodb_flush_log_at_trx_commit=1
sync_binlog=1




#Limit
max_connections =100


# PerThreadBySessionMemOption
join_buffer_size = 8M
sort_buffer_size = 8M
read_rnd_buffer_size = 8M
innodb_buffer_pool_size = 128M
binlog_cache_size= 8M




#Master
server_id=135                #服务器id
gtid_mode=on                 #开启gtid模式
enforce_gtid_consistency=on  #强制gtid一致性，开启后对于特定create table不被支持
log_bin=Master_bin


binlog_format=row            #强烈建议，其他格式可能造成数据不一致


max_binlog_size = 1024M
binlog_expire_logs_seconds= 172800  #tow day
binlog-ignore-db=information_schema
binlog-ignore-db=cluster
binlog-ignore-db=mysql
binlog-do-db=sharkdb




#SemiSyncOption
rpl_semi_sync_master_enabled = ON
rpl_semi_sync_master_timeout=10000 ##毫秒 Master Waite Salve tiemout
rpl_semi_sync_master_wait_no_slave=ON ##表示MASTER事务都要等待丛库确认收到

# 从库参数文件

vim etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid


port = 3306
character_set_server=utf8mb4
init_connect='SET NAMES utf8mb4'


log_timestamps = SYSTEM
default-time-zone = '+8:00'
skip-name-resolve


innodb_flush_log_at_trx_commit=1
sync_binlog=1


#Limit
max_connections =100


# PerThreadBySessionMemOption
join_buffer_size = 8M
sort_buffer_size = 8M
read_rnd_buffer_size = 8M
innodb_buffer_pool_size = 128M
binlog_cache_size= 8M


server_id=136                #服务器id
gtid_mode=on                 #开启gtid模式
enforce_gtid_consistency=on  #强制gtid一致性，开启后对于特定create table不被支持
log_bin=SALVE01_bin
binlog_format=row            #强烈建议，其他格式可能造成数据不一致
max_binlog_size = 1024M
binlog_expire_logs_seconds= 172800  #tow day






#Salve
relay_log=Relay01Log
skip_slave_start=1                  # skip_replica_start
log_slave_updates=1                 #log_replica_updates
slave-parallel-type=LOGICAL_CLOCK  #replica_parallel_type
slave_parallel_workers=16           #开启多线程复制
master_info_repository=TABLE
relay_log_info_repository=TABLE
relay_log_recovery=ON
read_only=ON
super_read_only = OFF
slave_net_timeout=60


sync_master_info=1000  #MasterInfo Per 1000 Event SyncTo Table
sync_relay_log=1000    #RelayLog Per 1000 Event Sync LogFile
sync_relay_log_info=100 #RelayLogInfo Per 1000 Event Syncto Table


skip_slave_start = OFF # on:Skip 'start savle' by mysqld start ; Off  BY START


replicate_do_db=sharkdb

#--主从都重启

[root@centos7-mysql8-master ~]# service mysqld start

10 从库配置复制参数

mysql> change master to master_host = '192.168.2.31', master_port = 3306,  master_user = 'Repl',    master_password = 'Shark@888', master_auto_position = 1;




mysql> start slave;
Query OK, 0 rows affected, 1 warning (3.59 sec)




mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: 192.168.2.31
                  Master_User: Repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: Master_bin.000010
          Read_Master_Log_Pos: 460
               Relay_Log_File: RelayLog.000002
                Relay_Log_Pos: 678
        Relay_Master_Log_File: Master_bin.000010
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: sharkdb
          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: 460
              Relay_Log_Space: 881
              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: 135
                  Master_UUID: 61c4b1bd-7331-11ed-8ac9-0800279840eb
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Replica 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: 61c4b1bd-7331-11ed-8ac9-0800279840eb:1
            Executed_Gtid_Set: 61c4b1bd-7331-11ed-8ac9-0800279840eb:1
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
       Master_public_key_path: 
        Get_master_public_key: 0
            Network_Namespace: 
1 row in set, 1 warning (0.00 sec)




ERROR: 
No query specified
 
mysql> show master status;
+---------------------+----------+--------------+------------------+------------------------------------------+
| File                | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+---------------------+----------+--------------+------------------+------------------------------------------+
| Salve_01_bin.000001 |      585 |              |                  | 61c4b1bd-7331-11ed-8ac9-0800279840eb:1-2 |
+---------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)
 
show global status like "%sync%";
+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| Innodb_data_fsyncs                         | 259   |
| Innodb_data_pending_fsyncs                 | 0     |
| Innodb_os_log_fsyncs                       | 91    |
| Innodb_os_log_pending_fsyncs               | 0     |
| Rpl_semi_sync_master_clients               | 0     |
| Rpl_semi_sync_master_net_avg_wait_time     | 0     |
| Rpl_semi_sync_master_net_wait_time         | 0     |
| Rpl_semi_sync_master_net_waits             | 0     |
| Rpl_semi_sync_master_no_times              | 1     |
| Rpl_semi_sync_master_no_tx                 | 1     |
| Rpl_semi_sync_master_status                | OFF   |
| Rpl_semi_sync_master_timefunc_failures     | 0     |
| Rpl_semi_sync_master_tx_avg_wait_time      | 0     |
| Rpl_semi_sync_master_tx_wait_time          | 0     |
| Rpl_semi_sync_master_tx_waits              | 0     |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0     |
| Rpl_semi_sync_master_wait_sessions         | 0     |
| Rpl_semi_sync_master_yes_tx                | 0     |
+--------------------------------------------+-------+
18 rows in set (0.01 sec)

11 同步测试

##主库操作

mysql> show master status;
+-------------------+----------+--------------+----------------------------------+----------------------------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB                 | Executed_Gtid_Set                      |
+-------------------+----------+--------------+----------------------------------+----------------------------------------+
| Master_bin.000010 |      460 | sharkdb      | information_schema,cluster,mysql | 61c4b1bd-7331-11ed-8ac9-0800279840eb:1 |
+-------------------+----------+--------------+----------------------------------+----------------------------------------+
1 row in set (0.00 sec)




mysql> create database sharkdb;
Query OK, 1 row affected (0.55 sec)

##从库查看

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: 192.168.2.31
                  Master_User: Repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: Master_bin.000010
          Read_Master_Log_Pos: 654
               Relay_Log_File: RelayLog.000002
                Relay_Log_Pos: 872
        Relay_Master_Log_File: Master_bin.000010
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: sharkdb
                   Last_Errno: 0
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 654
              Relay_Log_Space: 1075
              Until_Condition: None
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
               Last_SQL_Errno: 0
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 135
                  Master_UUID: 61c4b1bd-7331-11ed-8ac9-0800279840eb
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
           Retrieved_Gtid_Set: 61c4b1bd-7331-11ed-8ac9-0800279840eb:1-2
            Executed_Gtid_Set: 61c4b1bd-7331-11ed-8ac9-0800279840eb:1-2
                Auto_Position: 1
        Get_master_public_key: 0
1 row in set, 1 warning (0.01 sec)




ERROR: 
No query specified




mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sharkdb            |
| sys                |
+--------------------+
5 rows in set (0.02 sec)

##主库创建表测试

mysql> use sharkdb
Database changed
mysql> CREATE TABLE `big_table` (
  `uid` int(11) NOT NULL AUTO_INCREMENT,
  `mobile` char(11) DEFAULT NULL,
  `passwd` varchar(50) DEFAULT NULL,
  `name` varchar(50) DEFAULT NULL,
  `sex` tinyint DEFAULT NULL,
  `birthday` datetime DEFAULT NULL,
  `updated_time` datetime DEFAULT NULL,
  PRIMARY KEY (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

#从库查看

mysql> show tables;
+-------------------+
| Tables_in_sharkdb |
+-------------------+
| big_table         |
+-------------------+
1 row in set (0.01 sec)




mysql> desc big_table;
+--------------+-------------+------+-----+---------+----------------+
| Field        | Type        | Null | Key | Default | Extra          |
+--------------+-------------+------+-----+---------+----------------+
| uid          | int         | NO   | PRI | NULL    | auto_increment |
| mobile       | char(11)    | YES  |     | NULL    |                |
| passwd       | varchar(50) | YES  |     | NULL    |                |
| name         | varchar(50) | YES  |     | NULL    |                |
| sex          | tinyint     | YES  |     | NULL    |                |
| birthday     | datetime    | YES  |     | NULL    |                |
| updated_time | datetime    | YES  |     | NULL    |                |
+--------------+-------------+------+-----+---------+----------------+
7 rows in set (0.01 sec)

文章转载自海鲨数据库架构师，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

快速搭建MYSQL8.0主从关系

评论