mysql 同步使用者權限設定

主機1
Cd etc
Vi my.cnf

[mysqld]
server-id=1
log-bin=mysql-bin
binlog-do-db= test

/usr/share/mysql/mysql.server restart


mysql -u root

show master status;

slave stop;

CREATE USER 'XXXXX'@'XXX.XXX.XXX.XXX' IDENTIFIED BY 'XXXXXXX';
GRANT REPLICATION SLAVE ON *.* TO 'XXXXX'@'XXX.XXX.XXX.XXX';
FLUSH PRIVILEGES;

change master to master_host='XXX.XXX.XXX.XXX',
master_user='XXXXX',
master_password='XXXXX',
master_log_file='mysql-log.000001',
master_log_pos= 245;

slave start ;
show slave status\G;



主機2
Cd etc
Vi my.cnf

[mysqld]
server-id=2
replicate-do-db=adj
relay_log = test

/usr/share/mysql/mysql.server restart

mysql -u root –p

show master status;

slave stop;

CHANGE MASTER TO MASTER_HOST='XXX.XXX.XXX.XXX', MASTER_PORT=3306, MASTER_USER='XXXXX',MASTER_PASSWORD='XXXXX';

slave start ;
show slave status\G;




以上是MYSQL 兩主機同步
目前是有同步 可是主機1 刪除 他會跟著刪除資料
如果不讓她刪除資料 權限語法需要怎樣改
原本 GRANT REPLICATION SLAVE ON *.* TO 'XXXXX'@'XXX.XXX.XXX.XXX';
我嘗試改成 GRANT SELECT,INSERT,UPDATE SLAVE ON *.* TO 'XXXXX'@'XXX.XXX.XXX.XXX'; 無法修改
我該如何做 我希望他不能有刪除的功能 包含DROP TABLE 都不要有
我只單純想要把資料庫備份到另外一台主機
Sign In or Register to comment.