mysql 问题/技巧集合,持续更新...

1.mysql8安装centos7 rpm方式

下载

wget https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-8.0.23-1.el7.x86_64.rpm-bundle.tar

解压

tar -xvf mysql-8.0.23-1.el7.x86_64.rpm-bundle.tar

安装前准备

卸载mariadb-libs

rpm -qa | grep mariadb-libs
yum -y remove mariadb-libs-5.5.68-1.el7.x86_64

安装必要依赖

yum install libaio

安装

rpm -ivh mysql-community-common-8.0.23-1.el7.x86_64.rpm
rpm -ivh mysql-community-client-plugins-8.0.23-1.el7.x86_64.rpm 
rpm -ivh mysql-community-libs-8.0.23-1.el7.x86_64.rpm 
rpm -ivh mysql-community-client-8.0.23-1.el7.x86_64.rpm 
rpm -ivh mysql-community-server-8.0.23-1.el7.x86_64.rpm

启动

service mysqld start

查看root临时密码

grep "A temporary password" /var/log/mysqld.log

登陆

mysql -uroot -p

首次变更‘root'@'localhost'密码(密码包含大小写特殊字符)

ALTER USER 'root'@'localhost' IDENTIFIED BY 'Hahaha123456..';

设置远程登陆账号

CREATE USER 'root'@'%' IDENTIFIED BY 'Hahaha123.';

GRANT ALL ON *.* TO 'root'@'%';

端口开放

firewall-cmd --zone=public --add-port=3306/tcp --permanent
firewall-cmd --reload

2.mysql 数据库复制/备份

从远程复制数据库到本机，在本机执行命令

mysqldump -uroot -proot -h100.100.100.100 -P3366 --compress --databases test test1 test2 >back.sql

mysql -f -uroot -p  <back.sql

 从远程数据库备份到本机，在本机执行命令

mysqldump -uroot -proot -h100.100.100.100 -P3366 --compress --databases test test1 test2 | gzip > /home/backup/database_$(date +%Y%m%d_%H%M%S).sql.gz

3.mysql主从热备

主机配置(更改配置后重启mysql)

[mysqld]

log-bin=mysql-bin
#唯一就行
server-id=1

主机创建热备用户(指定Ip将%转成Ip)

create user 'slave'@'%' identified by 'Hahaha123.';
grant replication slave on *.* to 'slave'@'%';
flush privileges;

查看主机热备状态

show master status;

从机配置(更改配置后重启mysql)

[mysqld]

log-bin=mysql-bin
#唯一就行
server-id=2
#跳过所有错误，设置后可能会出现数据不一致的问题，谨慎设置
slave-skip-errors=all

从机上输入执行热备

change master to
    master_host='192.168.1.40',
    master_port=3066,
    master_log_file='mysql-bin.000003',
    master_log_pos=1438;

start slave user='root' password='Hahaha123.';

#注意此处是将用户名和密码直接放到启动主从命令上的，每次执行start slave都需要携带用户名和密码,不然就会出现Last_IO_Error: error connecting to master 'slave@127.0.0.1:3306' - retry-time: 60 retries: 1 message: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection.

master_log_file 为主机show master status 显示的文件名

master_log_pos 为主机show master status 显示的位置

其它命令

查看主状态  show master status;
查看从状态  show slave status;
开始从 start slave;
停止从  stop slave;
重置主从 reset slave; reset master;

  

关于非3306端口出现 Last_IO_Errno:2003    Connecting（在从机上执行此操作）

yum -y install policycoreutils-python

semanage port -a -t mysqld_port_t -p tcp 3066

主从报错[ERROR] [MY-010584] [Repl] Slave I/O for channel '': error connecting to master 'slave@127.0.0.1:3066' - retry-time: 60 retries: 10 message: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection. Error_code: MY-002061

错误原因：插件caching_sha2_password的介绍，这次故障的原因可以猜测为：在从库连接主库的时候使用的是不被 caching_sha2_password认可的RSA公钥，所以主库MySQL拒绝了数据库连接的请求，从而，从库报错’caching_sha2_password’ reported error:Authentication require secure connection。

解决方式：要从服务器请求RSA公钥，需要指定选项 --get-server-public-key 

mysql -uslave -p -h主库IP -P主库端口 --get-server-public-key

连接后服务器将RSA公钥发送给客户端，后者使用它来加密密码并将结果返回给服务器。插件使用服务器端的RSA私钥解密密码，并根据密码是否正确来接受或拒绝连接

之后再重新执行同步就可以了

stop slave;
reset slave;
change master to ...
start slave;