MySQL主从复制(开启GTID)

最新推荐文章于 2024-08-02 16:34:22 发布

一起讨论

最新推荐文章于 2024-08-02 16:34:22 发布

阅读量607

点赞数

分类专栏： MySQL Linux 文章标签： mysql linux 运维数据库 mariadb

本文链接：https://blog.csdn.net/lumia98/article/details/109869064

版权

Linux 同时被 2 个专栏收录

34 篇文章 0 订阅

订阅专栏

MySQL

7 篇文章 0 订阅

订阅专栏

Linux- Generic 方式安装MySQL

建议开启gtid模式，对于binlog数据恢复非常友好!

0、创建mysql目录脚本

#!/bin/bash
# By lumia98@vip.qq.com
# mysql二进制安装脚本，也适用多实例

# 创建安装目录
basedir='/opt/app/mysql-3306'   		  #mysql安装目录
datadir='/databases/mysql-3306/data'  #mysql数据存放目录
etcdir=${basedir}/etc                 #my.cnf存放目录
logdir=${basedir}/logs                #log存放目录
socketdir=${basedir}/socket           #pid、sock存放目录
binlogdir='/databases/mysql-3306/binlog' # binlog目录

echo '创建安装目录'
[ -d ${basedir} ] || mkdir -p ${basedir}

# 安装包信息
tar='mysql-8.0.20-linux-glibc2.12-x86_64.tar.xz'  # 安装包
app='mysql-8.0.20-linux-glibc2.12-x86_64'					# 解压后的app
sourcedir='/opt/soft'

echo '开始解压'
tar xf ${sourcedir}/${tar} -C ${sourcedir}

# 开始拷贝软件安装目录
cp -ar ${sourcedir}/${app}/*  ${basedir}

echo "创建数据存放目录"
[ -d ${datadir} ] || mkdir -p ${datadir}
[ -d ${etcdir} ]  || mkdir  ${etcdir}
[ -d ${logdir} ]  || mkdir  ${logdir}
[ -d ${socketdir} ] || mkdir ${socketdir}
[ -d ${binlogdir} ] || mkdir -p ${binlogdir}

# 删除自带的my.cnf
rm -rf /etc/my.cnf

1、主配置文件

cat > /opt/app/mysql-3306/etc/my.cnf <<EOF
[mysqld]

basedir=/opt/app/mysql-3306          # 安装目录

datadir=/databases/mysql-3306/data   # 数据库存放目录

socket=/opt/app/mysql-3306/socket/mysql.sock

pid-file=/opt/app/mysql-3306/socket/mysql.pid

log-error=/opt/app/mysql-3306/logs/mysql-error.log

server_id=3306

port=3306

secure-file-priv=/tmp   #新特性：只允许mysqld导入|导出到/tmp目录下

#autocommit=0  # 自动提交事物，根据场景需要，0代表关闭，1开启

# binlog
binlog_format = MIXED  					#binlog日志格式，mysql默认采用statement，建议使用mixed

max_binlog_size = 512M

# 5.7的binlog文件保存期限
#expire_logs_days = 15
# 8.0开始binlog文件保存期限
binlog_expire_logs_seconds = 2592000

binlog_cache_size = 4m           # binlog缓存大小

max_binlog_cache_size  = 512m    # 最大binlog缓存大小

log_bin=/databases/mysql-3306/binlog/mysql-bin  # binlog存放位置

# gtid开启
gtid-mode=on									# 启用gtid类型，否则就是普通的复制架构

enforce-gtid-consistency=true # 强制GTID的一致性

log-slave-updates=ON   				# slave更新是否记入日志

innodb_buffer_pool_size = 5734M # 等于物理内存的70%，8192*0.7

innodb_log_files_in_group = 3  # 为了提高性能，MySQL可以循环方式将日志写到多个文件

# 默认引擎
default-storage-engine = InnoDB
character-set-server = utf8mb4

# 配置超时
wait_timeout = 1800

# 禁用域名解析
skip_name_resolve = 1


[client]
socket=/opt/app/mysql-3306/socket/mysql.sock
default-character-set = utf8mb4

[mysql]
#prompt=db01 [\\d]>            # db01是主机
EOF

2、从库配置文件

cat > /opt/app/mysql-3306/etc/my.cnf <<EOF
[mysqld]

basedir=/opt/app/mysql-3306          # 安装目录

datadir=/databases/mysql-3306/data   # 数据库存放目录

socket=/opt/app/mysql-3306/socket/mysql.sock

pid-file=/opt/app/mysql-3306/socket/mysql.pid

log-error=/opt/app/mysql-3306/logs/mysql-error.log

server_id=3316

port=3306

secure-file-priv=/tmp   #新特性：只允许mysqld导入|导出到/tmp目录下

#autocommit=0  # 自动提交事物，根据场景需要，0代表关闭，1开启

# binlog
binlog_format = MIXED  					#binlog日志格式，mysql默认采用statement，建议使用mixed

max_binlog_size = 512M

# 5.7的binlog文件保存期限
expire_logs_days = 15
# 8.0开始binlog文件保存期限
# binlog_expire_logs_seconds = 2592000

binlog_cache_size = 4m           # binlog缓存大小

max_binlog_cache_size  = 128m    # 最大binlog缓存大小

log_bin=/databases/mysql-3306/binlog/mysql-bin  # binlog存放位置

# gtid开启
gtid-mode=on									# 启用gtid类型，否则就是普通的复制架构

enforce-gtid-consistency=true # 强制GTID的一致性

log-slave-updates=ON   				# slave更新是否记入日志


innodb_buffer_pool_size = 5734M # 等于物理内存的70%，8192*0.7

innodb_log_files_in_group = 3  # 为了提高性能，MySQL可以循环方式将日志写到多个文件

# 默认引擎
default-storage-engine = InnoDB
character-set-server = utf8mb4

# 配置超时
wait_timeout = 1800

# 禁用域名解析
skip_name_resolve = 1


[client]
socket=/opt/app/mysql-3306/socket/mysql.sock
default-character-set = utf8mb4

[mysql]
prompt=db01 [\\d]>            # db01是主机
EOF

3、创建用户授权及mysql初始化

useradd -s /sbin/nologin mysql

chown -R mysql.mysql /opt/app/mysql-3306

chown -R mysql.mysql /databases/mysql-3306

# 进入bin目录
cd /opt/app/mysql-3306/bin
# 开始初始化
./mysqld --defaults-file=/opt/app/mysql-3306/etc/my.cnf --basedir=/opt/app/mysql-3306 --datadir=/databases/mysql-3306/data --initialize-insecure  --user=mysql 

#  --initialize-insecure 初始化不生成密码，密码为空

4、启动MySQL

[root@localhost bin]# ./mysqld_safe  --defaults-file=/opt/app/mysql-3306/etc/my.cnf --datadir=/databases/mysql-3306/data

5、登录MySQL，主库创建账号

[root@test ~]#  /opt/app/mysql-3306/bin/mysql -uroot -p -S /opt/app/mysql-3306/socket/mysql.sock
 # 创建给从库用的账号
 mysql> grant replication slave  on *.* to repl@'192.168.0.%' identified by '123';
 
 # MySQL8.0创建创建方式
 mysql> create user 'repl'@'192.168.0.%'  IDENTIFIED WITH mysql_native_password BY '123456'; # 创建账号密码
 mysql>  GRANT replication slave on *.* TO 'repl'@'192.168.0.%';  # 授权

6、从库配置

mysql> change master to 
master_host='10.0.0.51',
master_user='repl',
master_password='123' ,
master_auto_position=1;  

# master_auto_position ： slave根据GTID自动选择适当的事务点进行复制

mysql> start slave;

7、从库查看是否成功

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.0.1
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000009
          Read_Master_Log_Pos: 1931
               Relay_Log_File: test-relay-bin.000003
                Relay_Log_Pos: 2146
        Relay_Master_Log_File: mysql-bin.000009
             Slave_IO_Running: Yes  # yes成功
            Slave_SQL_Running: Yes	# 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: 1931
              Relay_Log_Space: 3152
              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: 3306
                  Master_UUID: b09afa63-11d5-11eb-89c4-000c29f7a041  # gtid
             Master_Info_File: mysql.slave_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: b09afa63-11d5-11eb-89c4-000c29f7a041:1-11
            Executed_Gtid_Set: 061e3d05-11e6-11eb-bb0d-000c29f7a041:1-2,
b09afa63-11d5-11eb-89c4-000c29f7a041:1-11
                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 (0.00 sec)

报错集合

[root@test ~]# /opt/app/mysql-3306/bin/mysql -uroot -p
/opt/app/mysql-3306/bin/mysql: error while loading shared libraries: libtinfo.so.5: cannot open shared object file: No such file or directory

# Redhat解决方法 
ln -s /usr/lib64/libtinfo.so.6.1 /usr/lib64/libtinfo.so.5
# Ubuntu解决方法
sudo apt  install libncurses5 -y

[root@test ~]# /opt/app/mysql-3306/bin/mysql -uroot -p
Enter password: 
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

# 解决方法
 ln -s /opt/app/mysql-3306/socket/mysql.sock /tmp/