mysql数据库主从全机配置

| 2012年8月15日

今天倒蹬了一下mysql的数据库主从配置，记录一下

MYSQL主从配置

Master配置

vim /etc/my.cnf

server-id             = 1
log-bin=mysql-bin #二进制形式存储日志
binlog_ignore_db=mysql
binlog_ignore_db=information_schema
binlog_ignore_db=test

进入mysql,并查看master

mysql -uroot -p

设置从机权限（添加从机数据同步的帐号和密码）

mysql> grant replication slave on *.* to 'zhwen'@'172.24.28.192' identified by 'zhwen';

重启mysql 然后查看日志id:

mysql> show master status;
+------------------+----------+-----------------------+------------------+
| File             | Position | Binlog_Do_DB          | Binlog_Ignore_DB |
+------------------+----------+-----------------------+------------------+
| mysql-bin.000009 |      176 |                       |                  |
+------------------+----------+-----------------------+------------------+

把这个文件拷贝到从机的mysql的binlog目录下下面进入从机设置:

vim /etc/my.cnf

log-bin=mysql-bin

server-id       =2
binlog_ignore_db=mysql
binlog_ignore_db=information_schema
binlog_ignore_db=test
master-connect-retry=30

master-host=172.24.28.168 #主机地址
master-user=zhwen #主机用户名
master-password=zhwen #主机密码
read-only=1 #只允许读操作

重启mysql 进入从机mysql

mysql -uroot -p

mysql> Stop slave;
mysql> change master to master_user='zhwen';
mysql> change master to master_password='zhwen';
mysql> change master to master_host='172.24.28.168';
mysql> change master to master_log_file='mysql-bin.000009';
mysql> change master to master_log_pos=176;
mysql> start slave;

mysql> show slave status \G #查看其状态
以下两项都为yes即是从机配置成功的标志。
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

到这里，就基本配置已经完成了。可以进行一下测试在主库中创建一个数据库

create database helight;
Query OK, 1 row affected (0.00 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000009 |      265 |              |                  |
+------------------+----------+--------------+------------------+

然后到从机查询

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| helight            |
| mysql              |
| test               |
+--------------------+
4 rows in set (0.00 sec)
mysql> show slave status \G;
Relay_Master_Log_File: mysql-bin.000009
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: 265

看完本文有收获？请分享给更多人

关注「黑光技术」，关注大数据+微服务

MYSQL主从配置

Master配置

分类

标签