一、主库(10.159.186.149)配置
修改配置文件/etc/my.cnf：开启binlog日志，添加server_id 重启mysql:service mysql restart 登录mysql:mysql -u root -p 访问mysql库:use mysql; 新建存库可访问账号slave并赋予权限： select user,authentication_string,host from user; create user 'slaver'@'10.159.176.139' identified by 'slaver'; grant replication slave on *.* to 'slaver'@'10.159.176.139'; flush privileges; 检查账号: select user,authentication_string,host from user; show grants for 'slaver'@'10.159.176.139'; 检查主库的状态：show master status;二、从库(10.159.76.139)配置：
修改配置文件/etc/my.cnf:添加server_id,修改为只读(对root用户不生效) 重启mysql:service mysql restart 登录mysql:mysql -u root -p 检查slave从数据库额各参数 show variables like '%log_bin%'; show variables like '%server_id%'; 配置复制参数，slave从库连接master主库的配置 mySQL>change master to master_host='10.159.176.54',master_user='slaver',master_password='slaver',master_log_file='mysql-bin.000011',master_log_pos=432; 启动从库的同步开关 start slave; 查看复制状态 show slave status\G 设置只读账户： mysql> create user 'read_user'@'%' identified by '222222'; mysql> grant select on *.* to 'read_user'@'%'; mysql> flush privileges;三、mysql多线程复制配置(可以看作是基于table的)
修改从库配置文件/etc/my.cnf，添加以下内容： slave-parallel-type=LOGICAL_CLOCK slave-parallel-workers=16 # ###打开16个线程，原先的进程变成调度器 ###下面是优化参数 master_info_repository=TABLE relay_log_info_repository=TABLE relay_log_recovery=ON symbolic-links=0 查看： select * from slave_relay_log_info\G; show processlist;完整的配置文件
完整的从库配置文件(/etc/my.cnf) [mysqld] datadir=/usr/local/data/mysql-data basedir=/usr/local/src/mysql socket=/usr/local/data/mysql-data/mysql.sock user=mysql port=3306 character-set-server=utf8 # 取消密码验证 #skip-grant-tables # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 slave-parallel-type=LOGICAL_CLOCK slave-parallel-workers=16 # ###打开16个线程，原先的进程变成调度器 ###下面是优化参数 master_info_repository=TABLE relay_log_info_repository=TABLE relay_log_recovery=ON # skip-grant-tables server-id=2 read-only=true [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid 完整的主库配置文件(/etc/my.cnf) [mysqld] datadir=/usr/local/data/mysql-data basedir=/usr/local/src/mysql socket=/usr/local/data/mysql-data/mysql.sock user=mysql port=3306 character-set-server=utf8 # 取消密码验证 #skip-grant-tables # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 # skip-grant-tables server-id=1 log-bin=/usr/local/data/mysql-data/binlog/mysql-bin binlog-format=Row [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid四、异常信息
报错信息：The slave I/O thread stops because master and slave have equal MySQL server UUIDs; 解决：将mysql data目录下边的auto.cnf移除 mv /data/mysql-data/auto.cnf /data/mysql-data/auto.cnf.bak