202108190938 - mysql升级版本

1. before update

• 备份 [[202108161706 mysql备份方法]]
• 步骤：全局锁-> dump -> stop -> rename -> install -> initial -> start -> source
• 验证：随机抽取表

2. shutdown server

# 注意是哪个用户启动的服务 ;; 都是 root
root / mysql

# 添加全局锁  ;; 释放锁 unlock tables
/data01/mysql/bin/mysql -e "flush tables with read lock" -p'El#2lbQL[(@'
echo "lock all tables ... OK"

# 导出全库的数据
/data01/mysql/bin/mysqldump -h"127.0.0.1" -p"El#2lbQL[(@" --all-databases > /data01/data/bak/dump-20210823.sql
echo "dump all db .. OK"

# 停服
/data01/mysql/support-files/mysql.server stop
echo "server has stopped ... OK"

# rename
mv /data01/mysql /data01/mysql_20210824
echo "mysql has renamed ... OK"

# 备份my.cnf
cp /etc/my.cnf /data01/data/bak/my.cnf_20210824
echo "my.cnf has bakked ... OK "

3. upgrade

scp -P22 mysql-5.7.34-linux-glibc2.12-x86_64.tar.gz 192.168.169.100:$PWD

# 将新版本包解压
tar -zxvf /data01/app/src/mysql-5.7.34-linux-glibc2.12-x86_64.tar.gz -C /data01/
echo "解压完成 ，，"
mv /data01/mysql-5.7.34-linux-glibc2.12-x86_64 /data01/mysql
echo "rename  OK"
# 
mkdir /data01/mysql/data

chown -R mysql:mysql /data01/mysql
chmod -R 755 /data01/mysql
echo "dir   ok"

# 初始化
/data01/mysql/bin/mysqld --initialize --user=mysql --datadir=/data01/mysql/data/ --basedir=/data01/mysql/
echo "init    ok"

# 启动服务
/data01/mysql/support-files/mysql.server start
echo "start   ok"

echo " now  alter  root  passwd ,  then  source "
echo '/data01/mysql/bin/mysql -e "alter user root@localhost identified by B3" -p'
echo '/data01/mysql/bin/mysql -e "source /data01/data/bak/dump-20210820.sql" -p"B3"'
# 导入
#/data01/mysql/bin/mysql -e "source /data01/data/bak/dump-20210824.sql" -p"B3"

4. 主从同步

# 创建用户同步的用户，从数据库登录使用；并授权
CREATE USER 'copy1'@'192.168.169.2' IDENTIFIED BY 'B3';
GRANT REPLICATION SLAVE ON *.* TO 'copy1'@'192.168.169.2' IDENTIFIED BY 'B3';
flush privileges;

# 查看master状态，记住二进制文件名和位置；
 show master status;

# slave
source dumpfile ;
# 执行同步SQL 
 CHANGE MASTER TO 
 MASTER_HOST='192.168.169.2',
 MASTER_USER='copy1',
 MASTER_PASSWORD='B3',
 MASTER_LOG_FILE='mysql-bin.000044',
 MASTER_LOG_POS= 282946176 

 start slave;
 show slave status\G;
 # 当Slave_IO_Running和Slave_SQL_Running都为YES的时候就表示主从同步设置成功了。 

# 测试

5. 遇到的问题

5.1 被外网攻击

被bitcoin攻击，cloudera关停，将从库数据导出，导入主库；从库数据缺失

# 1. mysql权限控制,IP白名单

# 2. 从库监控，保证主从一致

5.2 主从不一致

Error 'Can't find any matching row in the user table' on query. Default database: 'mysql'. Query: 'GRANT ALL PRIVILEGES ON `dtest`.* TO 'jx'@'6.1.1.1' IDENTIFIED WITH 'mysql_native_password' AS '*718454DF82E2C055BEAC164E133A7C877E253989' WITH GRANT OPTION'


# 忽略错误, 1 表示跳过1个sql 
show slave status\G
stop slave ;
set global sql_slave_skip_counter =1 ;   
start slave ;
show slave status\G