mysql replication grant没同步_Mysql Replication机制主从同步实践



Mysql Replication机制主从同步实践

参考资料：

具体步骤：

1.Install mysql on 2 server:

apt-get install mysql-server

2.Set binary log and server-id in /etc/mysql/my.cnf

server1:

[mysqld]

log-bin=/var/log/mysql/mysql-bin.log

server-id=1

server2:

[mysqld]

log-bin=/var/log/mysql/mysql-bin.log

server-id=2

3.Using "mysql -u root -p" to connect to mysql, and run below command on slave sever2:

mysql> CREATE USERrepl@'%'IDENTIFIED BY 'slavepass';

mysql> GRANT REPLICATION SLAVE ON *.* TOrepl@'%';

4. Restart mysql on server1 and server2:

service mysql stop

service mysql start

5. Run command on master server1:

mysql> FLUSH TABLES WITH READ LOCK;

mysql> SHOW MASTER STATUS;

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

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |

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

| mysql-bin.000001 |      106 |              |                  |

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

1 row in set (0.00 sec)

6.Creating a Data Snapshot Using mysqldump on master server1:

shell> mysqldump -h 127.0.0.1 -u root -p 123456 --all-databases --master-data > dbdump.db

BTW, if we have new master or slave need to create, we can use shell> mysql -h master < dbdump.db

7.Setting the Master Configuration on the Slave server2:

mysql> CHANGE MASTER TO

->     MASTER_HOST='server1',

->     MASTER_USER='repl',

->     MASTER_PASSWORD='slavepass',

->     MASTER_LOG_FILE='mysql-bin.000001',

->     MASTER_LOG_POS=106;

8.Unlock tables on master server1:

mysql> UNLOCK TABLES;

9.Grant all permission for root on master server1:

>use mysql

>GRANT ALL ON *.* toroot@'%'IDENTIFIED BY '123456';

>FLUSH PRIVILEGES;

10.connect to master server1 on slave server2:

mysql -h server1 -u root -p

11.run below commands on slave server2:

mysql> start slave;

Query OK, 0 rows affected (0.01 sec)

mysql> SHOW SLAVE STATUS\G

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 10.185.98.24

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000003

Read_Master_Log_Pos: 825

Relay_Log_File: mysqld-relay-bin.000004

Relay_Log_Pos: 971

Relay_Master_Log_File: mysql-bin.000003

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 825

Relay_Log_Space: 1273

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: 0

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 0

Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 1

1 row in set (0.00 sec)

mysql>

12. Create a database on master:

mysql> create database test;

13. Check new database test sync to slave:

mysql> show databases;

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

| Database           |

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

| information_schema |

| mysql              |

| performance_schema |

| test               |

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

4 rows in set (0.00 sec)

mysql>

BTW, if you want to check all users on master, use command:select User,Host from mysql.user;