1.主从复制配置 a. 环境:CentOS7.4,IP地址分别是主库:192.168.11.146,从库:192.168.11.238, 主库版本应低于或等于从库版本,这里用的都是MySQL 8.0.13 b.主库配置/etc/my.cnf文件
[mysqld]
#一般配置选项 user=mysql port=3306 server_id=1 basedir= /usr/local/mysql datadir= /usr/app/mysqldata
character_set_server=utf8
pid_file=/usr/app/mysqldata/mysql.pid socket = /var/run/mysqld/mysqld.sock #错误日志 log_error= /usr/app/mysqldata/myerror.log #慢日志 slow_query_log=1 slow_query_log_file=/usr/app/mysqllog/mysql.slow long_query_time=5 #bin_log日志 log_bin=/usr/app/mysqllog/mysql_bin skip_external_locking skip_name_resolve #开启独立表空间 innodb_file_per_table = on
[mysql] no-auto-rehash socket = /var/run/mysqld/mysqld.sock #default-character-set=utf8 #safe-updates
[client] loose-default-character-set = utf8
重启MySQL服务
/etc/init.d/mysqld start
授权slvave 用户
grant replication slave,replication client on . to 'slvae_rp' identified by '111111';
flush privileges;
从库配置/etc/my.cnf文件
[mysqld]
#一般配置选项 user=mysql port=3306 server_id=1 basedir= /usr/local/mysql datadir= /usr/app/mysqldata
character_set_server=utf8
pid_file=/usr/app/mysqldata/mysql.pid
socket = /var/run/mysqld/mysqld.sock
#错误日志
log_error= /usr/app/mysqldata/myerror.log
#慢日志
slow_query_log=1
slow_query_log_file=/usr/app/mysqllog/mysql.slow
long_query_time=5
#bin_log日志
log_bin=/usr/app/mysqllog/mysql_bin
#开启中继日志
relay_log=/var/lib/mysql/relay-log
log_slave_updates=1 #从服务器将其SQL线程执行的更新记入到从服务器自己的二进制日志
read_only=1 #只读模式
skip_external_locking skip_name_resolve #开启独立表空间 innodb_file_per_table = on
[mysql] no-auto-rehash socket = /var/run/mysqld/mysqld.sock #default-character-set=utf8 #safe-updates
[client] loose-default-character-set = utf8
重启mysql服务 /etc/init.d/mysqld restart
主库: show master status;
从库: change master to master_host='192.168.11.146', master_port=3306, master_user='slave_rp', master_password='111111', master_log_file='mysql-bin.000001', master_log_pos=154; 开启从库slava start slave; 查看从库状态 show slave status;
确认正常: slave_io_running:yes slave_sql_running:yes seconds_behind_master 0
2.主从复制原理
从库生成两个线程,一个I/O线程,一个SQL线程;
i/o线程去请求主库 的binlog,并将得到的binlog日志写到relay log(中继日志) 文件中; 主库会生成一个 log dump 线程,用来给从库 i/o线程传binlog;
SQL 线程,会读取relay log文件中的日志,并解析成具体操作,来实现主从的操作一致,而最终数据一致;