mysql 倒库_[学习笔记]mysql主从配置

环境：2台虚拟机 centos X64 已经编译安装好了lamp

master:

1.编辑my.cnf文件在[mysqld]下添加2行参数

(2行参数一定要放在mysqld下  否则会出错)

server-id = 1

log-bin = /data/3307/mysql-bin

重启mysql！

2.创建用于同步的用户

进入主数据库执行命令：

mysql> grant replication slave on *.* to 'rep'@'192.168.137.%' identified by "pizize";

(

#replication slave 为mysql同步的必须权限。

#*表示所有表和库

#'rep'@'192.168.137.%' rep为同步账号。192.168.137.%表示允许整个137网段的IP都可以访问

)

对数据库锁表只读

mysql> flush tables with read lock;

(锁表后，不能再创建表，也不能插入数据，锁表命令时间会受下面参数控制，若果超过设置时间不操作会自动解锁

interactive_timeout = 60

wait_timeout = 60

查看默认时长(秒)：mysql> show  variables like '%timeout%';

解锁命令：unlock tables;

)

锁表后用show master status; 查看主库的日志文件名和偏移量

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

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |

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

| mysql-bin.000001 |     106 |              |                  |

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

slave

3.导出数据库 把它传入从库

编辑my.cnf 设置server-id的值并关闭binlog设置

如果数据库以前配置过主从 那么要先执行 stop slave;否则就会报错

mysql> CHANGE MASTER TO

-> MASTER_HOST='192.168.137.112',

-> MASTER_PORT=3307,

-> MASTER_USER='rep',

-> MASTER_PASSWORD='pizize',

-> MASTER_LOG_FILE='mysql-bin.000001'

-> MASTER_LOG_POS=106;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'MASTER_LOG_POS=106' at line 7

mysql> stop slave;

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CHANGE MASTER TO MASTER_HOST='192.168.137.112', MASTER_PORT=3307, MASTER_USER='rep', MASTER_PASSWORD='pizize', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=106;

Query OK, 0 rows affected (0.03 sec)

mysql> START SLAVE;

Query OK, 0 rows affected (0.00 sec)

mysql> SHOW SLAVE STATUS\G;

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

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.137.112

Master_User: rep

Master_Port: 3307

Connect_Retry: 60

Master_Log_File: mysql-bin.000002

Read_Master_Log_Pos: 106

Relay_Log_File: lamp113-relay-bin.000003

Relay_Log_Pos: 251

Relay_Master_Log_File: mysql-bin.000002

Slave_IO_Running: Yes #IO线程请求日志。sql线程表示应用日志。

Slave_SQL_Running: Yes  #当这2个项为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: 106

Relay_Log_Space: 553

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 #这个数字如果不为0的话，表示主库和从库有延迟

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 0

Last_SQL_Error:

1 row in set (0.00 sec)

倒库完成发现没问题之后再到主库解锁，unlock tables;

内容来自参加老男孩linux运维培训，学习整理部分内容