grant replication slave on *.*
给从库一个复制binlog的账号
配置复制，并启动
从库上配置复制信息，并指向master
查看主从复制信息
show slave status \G
1）备份还原
主：101
从：100
a)主库备份
aiapple@Ubuntu:~$ mysqldump -uroot -p --socket=/tmp/mysqldata/node1/mysql.sock --master-data --all-databases > all_master.sql --master-date:记录备份时binlog位置 aiapple@ubuntu:~$ cat all_master.sql | less -- -- Position to start replication or point-in-time recovery from -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=6125; -- -- Current Database: `db1` --
b)从库还原
主库远程连接到从库，使用source还原
从库添加主库白名单：
mysql> grant all on *.* to root@192.168.1.101 WITH GRANT OPTION;
设置密码：
mysql> set password for root@'localhost'=password('000000');
Query OK, 0 rows affected (0.00 sec)
主库添加从库白名单：
mysql> grant all on *.* to root@192.168.1.100 identified by '000000' with grant option;
主库登陆从库并还原数据：
aiapple@ubuntu:~$ mysql -uroot -p -h 192.168.1.100 mysql> source all_master.sql
2）赋权 replication slave
mysql> grant replication slave on *.* to repl@192.168.1.100 identified by 'repl'; Query OK, 0 rows affected (0.00 sec)
3) 从库配置复制
#查看帮助信息 ？ change master to mysql> change master to MASTER_USER='repl'; Query OK, 0 rows affected, 2 warnings (0.03 sec) mysql> change master to MASTER_PASSWORD='repl'; Query OK, 0 rows affected, 2 warnings (0.02 sec) mysql> change master to MASTER_HOST='192.168.1.101'; Query OK, 0 rows affected (0.03 sec) mysql> change master to MASTER_LOG_FILE='mysql-bin.000001';
4）启动：
mysql> start slave; Query OK, 0 rows affected (0.01 sec) mysql> show slave status\G; slave_io_running:yes slave_sql_running:yes #表示配置成功 mysql> show processlist; +----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+ | 2 | root | localhost | NULL | Query | 0 | init | show processlist | | 16 | system user | | NULL | Connect | 60 | Waiting for master to send event | NULL | | 17 | system user | | NULL | Connect | 60 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL | +----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+
注意：
主从server_id应该不同；
主从开启binlog日志
MASTER_LOG_FILE指定主库bin_log第一个文件；
半同步复制
配置mysql半同步复制 semi-sync
查看有哪些插件
show plugin
1.主从异步复制搭建
1)主库全备，备库恢复 mysqldump -uroot -p123456 --socket=/data/mysql/node1/mysqld.sock --single-transaction -A --master-data=1 > all_db.sql mysql -utest -ptest -h(从库IP) -P3306 mysql>source all_db.sql; 2)主库授权用户 grant replication slave on *.* to repl@'(从库IP)' identified by 'repl'; 3)从库配置复制 less all_db.sql|grep "change master to" change master to master_host='(主库IP)',master_user='repl',master_password='repl',master_log_file='XXX',master_log_pos=XXX; start stave; show slave status\G 4)复制检验 主库： use db1; insert into t1 values(10); 从库： use db1; select * from t1;(获得数据) 主库： drop database db2; 从库： show databases;(显示db2被删除) 5)查看线程 主库：show processlist;(dump线程) 从库：show processlist;(IO线程、SQL线程) 6)查看日志 从库：cd /data/mysql/node1 cat master.info cat relay-log.info
2.主从半同步复制安装