mariadb主从

----------------------------------------------------------------主-----------------------------------------------------------------------------

vim /etc/my.cnf +10

i

log-bin=mysql-bin　　　　　　　　　　　　/file名称

binlog-ignore-db=information_schema　　　　/不同步的数据库

binlog-ignore-db=mysql

binlog-ignore-db=performance_schema

server-id= 1　　　　　　　　　　　　　　　　//id必须唯一

 

----------------------------------------------------------------从-----------------------------------------------------------------------------

vim /etc/my.cnf +10

i

server-id=2　　//id必须唯一

read_only = 1     　　//只读

 ----------------------------------------------------------------重启服务-----------------------------------------------------------------------------

systemctl restart mariadb

 ----------------------------------------------------------------主库-----------------------------------------------------------------------------

mysql -p123qwe

CREATE USER 'abc'@'%' IDENTIFIED BY '123qwe';                           /创建用户

grant replication slave on *.* to 'abc'@'172.16.0.227' identified by '123qwe';　　　　　　　　　　　　　　//授权用户

flush privileges;　　　　　　　　　　　　//刷新权限

show master status;　　　　　　　　　　//show master 的文件名和偏移量

 ----------------------------------------------------例表---------------------------------------------------------------------------------

MariaDB [(none)]> show master status;                                      

+------------------+----------+--------------+---------------------------------------------+

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB                            |

+------------------+----------+--------------+---------------------------------------------+

| testsql.000001 |      5416 |              | information_schema,mysql,performance_schema |

+------------------+----------+--------------+---------------------------------------------+

1 row in set (0.00 sec)

 

 ----------------------------------------------------------------从-----------------------------------------------------------------------------

mysql -p123qwe

stop slave;

CHANGE MASTER TO MASTER_HOST='172.16.0.226', MASTER_USER='abc',  MASTER_PASSWORD='123qwe', MASTER_LOG_FILE='testsql.000001', MASTER_LOG_POS=5416;

start slave;        //启动之前手动复制一下数据库，备份，恢复操作

show slave status\G;

 主库查权限

select host,user from mysql.user;   

delete from mysql.user where user='xxx';

排错查询

show master status;

show variables like 'server_id';

 ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------

stop slave;
set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
start slave;