一、主从原理
图中组件说明:
1.二进制日志文件(Binary log)
主库中二进制日志文件默认关闭。开启:/etc/my.cnf里的log-bin=mysql-bin
2.IO线程/Sql线程
只有实现了主从同步的配置后,才会开启这两个线程.
3.中继日志(Relay log)
中继日志是自动生成的,主要的作用就是暂时保存二进制文件的信息的.
图中原理说明:
1.当主库发生了数据”更新”时,将更新的消息写入二进制日志文件中.
2.从库通过IO线程实时的读取主库的二进制日志文件中更新的内容.
3.IO线程将读取到的信息写入中继日志中.
4.Sql线程会实时的读取中继日志中的信息,实现数据入库处理(replay),实现主从数据同步.
二、主从步骤
1.安装mysql. 顺序:debuginfo-shared-client-server rpm -ivh
这方法太low
先进方法参考:
彻底删除mysql:https://www.jianshu.com/p/8130ba0f25bb
安装mysql:
rpm -ivh https://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm
yum repolist all | grep mysql | grep enabled
yum -y install mysql-community-server
启动数据库:
systemctl start mysqld
显示密码:
cat /var/log/mysqld.log | grep -i 'temporary password'
开启权限:
set global validate_password_policy=0;
set global validate_password_length=4;
修改密码:
alter user '要修改密码的用户'@'localhost' identified by '新密码';
开启mysql远程访问权限:
grant all on *.* to 'root'@'%' identified by '123456';
增加用户:
GRANT USAGE ON *.* TO '新用户'@'localhost' IDENTIFIED BY '新密码' WITH GRANT OPTION;
刷新权限:
flush privileges;
查看新用户权限
show grants for '新用户'@'%'
给root或新用户授权
grant all privileges ON *.* TO '新用户'@'%';
#查看数据库编码格式
mysql>SHOW VARIABLES LIKE 'char%';
设置mysql开机自启动
chkconfig mysqld on
准备工作
1.vi /etc/my.cnf:
[client]
default-character-set = utf8
socket = /var/lib/mysql/mysql.sock
[mysqld]
user = mysql
port = 3306
socket = /var/lib/mysql/mysql.sock
datadir = /var/lib/mysql
server-id = 1
log-bin = mysql-bin
log-slave-updates = 1
auto-increment-offset = 1
auto-increment-increment = 2
expire_logs_days= 7
max_binlog_size= 100m
binlog_cache_size= 4m
max_binlog_cache_size= 512m
binlog_format = mixed
binlog-do-db = 你要做主从的数据库名称!!!
open_files_limit = 10240
character_set_server=utf8mb4
max_connections=2000
innodb_log_file_size=256M
max_allowed_packet = 34M
#skip-grant-tables
default-time_zone = '+8:00'
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
需要重启mysql: systemctl restart mysqld
#看到mysql目录下有mysql-bin.000001和mysql-bin.index 表示启动成功
#从机的server-id是2
搭建主从
主机:
设置权限:
set global validate_password_policy=0;
set global validate_password_length=4;
#命令行远程连接mysql:mysql -h127.0.0.1 -uroot -p123456
主机显示二进制文件名称和位置:
show master status;
从机:
change master to master_host="127.0.0.1",
master_port=3306,
master_user="root",
master_password="root",
master_log_file="mysql-bin.000001",
master_log_pos=154;
#开启主从服务
start slave
#检测线程启发启用
show slave status
#看到slave_IO_running和slave_SQL_running都显示yes就可以了
PS:mysql启动报异常如何处理
1.PID/socket错误
mysql #kill -9 6662 #假设mysql的服务id号是6662
2.删除二进制日志文件
/var/lib/mysql中
mysql #rm -rf mysql-bin.*