安装文档Mysql及其主从复制配置

检查安装

rpm -qa | grep -i mysql
yum list installed | grep mysql
systemctl status mysqld.service

卸载

systemctl stop mysqld.service
yum remove mysql-xxx mysql-xxx mysql-xxx mysqk-xxxx
find / -name mysql
rm -rf xxx
rm -rf /etc/my.cnf

安装

依赖检查
rpm -qa|grep libaio
rpm -qa|grep net-tools
rpm -qa|grep net-tools

 rpm -ivh mysql-community-client-plugins-8.0.25-1.el7.x86_64.rpm
 	yum remove mysql-libs
 rpm -ivh  mysql-community-common-8.0.25-1.el7.x86_64.rpm
 rpm -ivh mysql-community-libs-8.0.25-1.el7.x86_64.rpm
 rpm -ivh  mysql-community-client-8.0.25-1.el7.x86_64.rpm 
 	yum -y install numactl
 rpm -ivh  mysql-community-server-8.0.25-1.el7.x86_64.rpm
 
 查看 mysql --version

 服务自启
 systemctl list-unit-files|grep mysqld.service
 	systemctl enable mysqld.service
 	systemctl disable mysqld.service
启动：systemctl start mysqld.service 
关闭：systemctl stop mysqld.service 
重启：systemctl restart mysqld.service 
查看状态：systemctl status mysqld.service

初始化
 mysqld --initialize --user=mysql
 cat /var/log/mysqld.log
 mysql -hlocalhost -P3306 -uroot -p 
 ALTER USER 'root'@'localhost' IDENTIFIED BY 'qwaszx12';
 use mysql;
 update user set host = '%' where user ='root';
 flush privileges;
mysql8额外配置：
 ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'qwaszx12';

 systemctl stop firewalld.service
 systemctl disable firewalld.service
 firewall-cmd --add-port=3306/tcp --permanent
 firewall-cmd --reload
 

主从复制配置

一主一从 Slave 会从 Master 读取 binlog 来进行数据同步。

修改MySQL Server 的UUID方式：
vim /var/lib/mysql/auto.cnf 
systemctl restart mysqld

主机配置 vim /etc/my.cnf
#[必须]主服务器唯一ID 
server-id=1 
#[必须]启用二进制日志,指名路径。比如：自己本地的路径/log/mysqlbin 
log-bin=mysql
#[可选] 0（默认）表示读写（主机），1表示只读（从机） 
read-only=0 
#设置日志文件保留的时长，单位是秒 
binlog_expire_logs_seconds=6000 
#控制单个二进制日志大小。此参数的最大和默认值是1GB 
max_binlog_size=200M 
#[可选]设置不要复制的数据库 binlog-ignore-db=test 
#[可选]设置需要复制的数据库,默认全部记录。比如：
binlog-do-db=test
#[可选]设置binlog格式 
binlog_format=STATEMENT

systemctl restart mysqld.service

从机配置文件 vim /etc/my.cnf
#[必须]从服务器唯一ID 
server-id=2
#[可选]启用中继日志 
relay-log=mysql-relay

binlog格式设置

#基于SQL语句的复制
binlog_format=STATEMENT
#ROW模式
binlog_format=ROW
#混合模式复制
binlog_format=MIXED

systemctl status firewalld.service #CentOS 7

#主机：建立账户并授权
 mysql -hlocalhost -P3306 -uroot -p
#在主机MySQL里执行授权主从复制的命令  #5.5,5.7
GRANT REPLICATION SLAVE ON *.* TO 'slave1'@'从机器数据库IP' IDENTIFIED BY 'abc123'; 

#mysql8
CREATE USER 'slave1'@'%' IDENTIFIED BY '123456';
GRANT REPLICATION SLAVE ON *.* TO 'slave1'@'%'; 
show grants for 'slave1'@'%';
#此语句必须执行。否则见下面。 
ALTER USER 'slave1'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
flush privileges;

show master status;查询Master的状态，并记录下File和Position的值

mysql.000001 | 1136

#从机：配置需要复制的主机
CHANGE MASTER TO MASTER_HOST='主机的IP地址', MASTER_USER='主机用户名', MASTER_PASSWORD='主机用户名的密码', MASTER_LOG_FILE='mysql-bin.具体数字', MASTER_LOG_POS=具体值;
 mysql -hlocalhost -P3306 -uroot -p
CHANGE MASTER TO MASTER_HOST='119.45.9.98',MASTER_USER='slave1',MASTER_PASSWORD='123456',MASTER_LOG_FILE='mysql.000001',MASTER_LOG_POS=1136;
#启动slave同步 
START SLAVE;
reset slave; #报错则删除SLAVE数据库的relaylog日志文件，并重新启用新的relaylog文件
SHOW SLAVE STATUS\G; 查看同步状态

测试

主机：
CREATE DATABASE test;
use test;
 show tables;
CREATE TABLE mytbl(id INT,NAME VARCHAR(16)); 
INSERT INTO mytbl VALUES(1, 'zhang3'); INSERT INTO mytbl VALUES(2,@@hostname);
select * from mytbl;

stop slave;