mysql+主从同步+用户,MySQL主从同步

所采用的mysql数据库是rpm包方式安装

1. Master:修改/etc/my.cnf文件[mysqld]

[mysqld]

server-id = 1

log-bin

innodb_flush_log_at_trx_commit=1

sync_binlog=1

datadir=/var/lib/mysql

2. Slave:修改/etc/my.cnf文件[mysqld]

[mysqld]

server-id=2

datadir=/var/lib/mysql

3. Master:在master数据库设置用来同步的slave用户权限

GRANT REPLICATION SLAVE ON *.*

TO ''@''

IDENTIFIED BY '';

例: grant replication slave on *.* to 'repl'@'192.168.1.2' identified by '123456';

4.Master上锁表：flush tables with read lock；

解锁：unlock tables；

5. Master数据导到slave

打包/var/lib/mysql目录，然后scp拷贝到从库上，覆盖从库/var/lib/mysql目录

6. Master查看日志节点和Position信息

查看主库信息获得日志文件以及position

mysql> show master status;

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

| File             | Position   | Binlog_Do_DB | Binlog_Ignore_DB |

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

| mysql-bin.000596 | 1050931156 |              |                  |

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

7.从库同步

mysql>STOP SLAVE；

mysql>CHANGE MASTER TO

>MASTER_HOST='',

>MASTER_USER='',

>MASTER_PASSWORD='',

>MASTER_LOG_FILE='',

>MASTER_LOG_POS=;

mysql>START SLAVE;

执行完上述命令后

mysql> show slave status \G

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

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.1.2

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000596

Read_Master_Log_Pos: 1036007396

Relay_Log_File: localhost-relay-bin.000221

Relay_Log_Pos: 1036007533

Relay_Master_Log_File: mysql-bin.000596

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: 1036007396

Relay_Log_Space: 1036007533

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

1 row in set (0.00 sec)

同步完成