ITPub博客

首页 > 数据库 > MySQL > MySQL主从复制配置

MySQL主从复制配置

原创 MySQL 作者:fiona8953 时间:2015-05-06 14:59:24 0 删除 编辑
Master
192.168.102.168 

[mysql@fiona bin]$ mysqladmin -uroot -p version
Enter password: 
mysqladmin  Ver 8.42 Distrib 5.5.39, for Linux on x86_64
Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.

Server version          5.5.39-log
Protocol version        10
Connection              Localhost via UNIX socket
UNIX socket             /usr/local/mysql/mysql.sock
Uptime:                 44 min 15 sec

Threads: 1  Questions: 9  Slow queries: 0  Opens: 33  Flush tables: 1  Open tables: 26  Queries per second avg: 0.003

Slave
192.168.102.130
[root@fiona2 home]# mysqladmin -uroot -p version
mysqladmin  Ver 8.42 Distrib 5.1.71, for redhat-linux-gnu on x86_64
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.

Server version          5.5.39-log
Protocol version        10
Connection              Localhost via UNIX socket
UNIX socket             /usr/local/mysql/mysql.sock
Uptime:                 5 min 2 sec

Threads: 1  Questions: 2  Slow queries: 0  Opens: 33  Flush tables: 1  Open tables: 26  Queries per second avg: 0.006

Steps:
//查看是否启用了日志
mysql> show variables like 'log_bin' ;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set (0.01 sec)

//查看master的status
mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000019 |      107 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

//切换到data下面用mysqlbinlog查看bin log
[root@fiona ~]# cd /usr/local/mysql/data
[root@fiona data]# mysqlbinlog mysql-bin.000002 | tail -20
SET TIMESTAMP=1408843507/*!*/;
COMMIT
/*!*/;
# at 1070530
#140824  9:25:07 server id 1  end_log_pos 1070549       Stop
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

mysql> use test
Database changed
mysql> show tables;
Empty set (0.00 sec)


mysql> create table TEST(
    -> ID bigint not null primary key,
    -> NAME varchar(15) not null,
    -> EMAIL varchar(128) not null);
Query OK, 0 rows affected (0.04 sec)
mysql> insert into TEST (ID,NAME,EMAIL) values ('001','AAA','aaa@163.com');
Query OK, 1 row affected (0.03 sec)
mysql> insert TEST (ID,NAME,EMAIL) values ('003','CCC','ccc@163.com'),('002','BBB','bbb@163.com');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0



有个小技巧跟大家介绍下,我在准备转换的时候发现日志文件有2G多,寻思着为什么mysql为什么不把日志按日志定期的分多个文件放呢。结果发现mysql有个更好的方法,可以通过时间参数获取某个时间段的数据,例子如下:
mysqlbinlog   --start-datetime="2010-11-20 00:00:00"  --stop-datetime="2010-11-21 00:00:00" 

Reference:http://zhidao.baidu.com/link?url=FjBsrSN50NK2f1sE8F_krGBldO6p1etu0dUw846x0XnF6jGBPxRA3ue49Ou4GprfFGaj39PSCH4ZrxpHpQl3Ch5uFSHt0nMCYlLO2SXJIea
Mysql5.5配置主从复制
http://ylw6006.blog.51cto.com/470441/888523

MYSQL启动后报:ERROR! The server quit without updating PID file错误的问题解决
Reference:http://blog.csdn.net/faye0412/article/details/7038290


来自 “ ITPUB博客 ” ,链接:https://blog.itpub.net/26477398/viewspace-1625815/ ,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2014-06-13

  • 博文量
    284
  • 访问量
    222494