MySQL 用户授权和bin-log日志和主从复制、读写分离（2）

最新推荐文章于 2023-08-21 09:15:00 发布

domorejojo

最新推荐文章于 2023-08-21 09:15:00 发布

阅读量1.4k

点赞数

分类专栏： MySQL

本文链接：https://blog.csdn.net/think2me/article/details/8631272

版权

MySQL 专栏收录该内容

12 篇文章 1 订阅

订阅专栏

前言：

前面一篇，我们ok了Ubuntu上Lamp的搭建以及MySQL·的用户授权和简单的bin-log日志的了解，这一篇，我们继续上面的，然后把主从复制完成。

1. Mysql数据库的数据备份、恢复以及bin-log恢复。

在MySQL中备份数据是一项非常重要的事情，因为不知道什么时候数据崩溃或者其他的原因导致数据丢失。备份的方法有很多。最常见的就是mysqldump命令了。

一，mysqldump 备份数据。

mysql> select * from t1;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    5 |
|    4 |
+------+
5 rows in set (0.00 sec)

mysql> reset master;
Query OK, 0 rows affected (0.02 sec)

mysql> flush logs;
Query OK, 0 rows affected (0.02 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 |      106 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

mysql>

t1 表中有5条数据，先清空所有的lgs; reset master。然后再建立一个新的bin-log ：flush logs：

备份开始： mysqldump -uroot -p123456 test -l -F > /tmp/dumpsql/xx.sql

root@yangyi:/tmp/dumpsql# mysqldump -uroot -p123456 test -l -F >/tmp/dumpsql/test.sql

成功备份test数据库！

-l 表示库锁定，不能写入，

-F 相当于flush logs；新建一个bin-log日志，这样，就起了一个保护作用，新的增删改就会到新的bin-log 里面

二，mysqldump 和bin-log日志恢复数据库。

刚才备份了test数据库，那么如果我们备份时是10:00，里面只有5条数据，那如果在10:30的时候，我们又新增了5条数据，然后数据库崩溃了，删除了。如果我们恢复数据，只能恢复5条数据，那10:30的那5条怎么回复了，所以可以用bin-log 日志恢复。

下面我们模拟增加数据，增加5条数据。

mysql> select * from t1;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    5 |
|    4 |
+------+
5 rows in set (0.00 sec)

mysql> insert into t1 values(6),(7),(8),(9),(10);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql>

删除数据：

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 |      210 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

mysql> flush logs;
Query OK, 0 rows affected (0.02 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000004 |      106 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

mysql> drop table t1;
Query OK, 0 rows affected (0.07 sec)

mysql> select * from t1;
ERROR 1146 (42S02): Table 'test.t1' doesn't exist
mysql>

数据库表t1 被删除了，且bin-log在00004之中，这种情况较简单，适合实验。

下面开始恢复数据：mysql -uroot -p123456 test < /tmp/dumpsql/test.sql

root@yangyi:/tmp/dumpsql# mysql -uroot -p123456 test < /tmp/dumpsql/test.sql

root@yangyi:/tmp/dumpsql# mysql -uroot -p123456
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 88
Server version: 5.1.61-0ubuntu0.10.10.1-log (Ubuntu)

Copyright (c) 2000, 2011, 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.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select * from t1;
ERROR 1046 (3D000): No database selected
mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from t1;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    5 |
|    4 |
+------+
5 rows in set (0.00 sec)

mysql>

看了一下只有5条数据，那么10:30插入的5条数据却没有恢复，所以，只能依靠bin-log了

root@yangyi:/var/log/mysql# mysqlbinlog mysql-bin.000003|mysql -uroot -p123456 test;

ok。查看一下数据库。

mysql> select * from t1;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    5 |
|    4 |
|    6 |
|    7 |
|    8 |
|    9 |
|   10 |
+------+
10 rows in set (0.00 sec)

成功恢复所有的数据。

三，mysqlbinlog 后面的参数。

start-position 和stop-position表示恢复从第几步到几步。

start-date和stop-date 表示恢复的起始位置。

2.MySQL主从复制

靠，说了好久，终于说到主题上面来了，真不容易。o(>_<)o

先使用scp 管道复制，将master上备份的数据复制到slave 上。

mysql> reset master;
Query OK, 0 rows affected (0.07 sec)
mysql> select * from test.t1;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    5 |
|    4 |
|    6 |
|    7 |
|    8 |
|    9 |
|   10 |
|   11 |
+------+
11 rows in set (0.00 sec)

mysql>

清掉bin-log，看一下10条数据。

备份：

root@yangyi:/var/log/mysql# mysqldump -uroot -p123456 test -l -F >/tmp/dumpsql/test.sql
root@yangyi:/var/log/mysql#

复制到slave 服务器：使用scp管道复制：

root@yangyi:/# scp /tmp/dumpsql/test.sql yangyi@192.168.15.133:/tmp/
yangyi@192.168.15.133's password: 
test.sql                                   100% 1831     1.8KB/s   00:00    
root@yangyi:/#

ok 。复制成功。如果出现问题，那就ssh没有开启了。明天更新一篇Ubuntu下无法apt-get ssh的办法。

然后，登录到slave 服务器，看是test.sql是后已经复制过去

root@yangyi:/# ls /tmp/
hsperfdata_yangyi
keyring-3fr3em
orbit-gdm
orbit-yangyi
OSL_PIPE_1000_SingleOfficeIPC_fc2ca3ce7a011ffa1fe5ba87651d1e
pulse-J34GKZW8iOhg
pulse-PKdhtXMmr18n
ssh-FoXYjp2168
svejh.tmp
test.sql
virtual-yangyi.CR6mwB
VMwareDnD
vmware-root
vmware-yangyi
root@yangyi:/#

OK ,test.sql 已经成功通过scp 复制过来，接下面就把test.sql 恢复到slave 服务器。

root@yangyi:/# mysql -uroot -p123456 test < /tmp/test.sql 
ERROR 1049 (42000): Unknown database 'test'

提示说没有test数据库，恩，那就进去新建一个数据库，名叫test好了。然后再恢复一下，看看

mysql> select * from test.t1;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    5 |
|    4 |
|    6 |
|    7 |
|    8 |
|    9 |
|   10 |
|   11 |
+------+
11 rows in set (0.18 sec)

mysql>

恩。数据主服务备份的数据已经成功复制恢复到slave服务器。

接下来就是真正的主从复制，，，，，，，，，，，，，，，，，，，，o(>_<)o ，，，，，，，，，，，，，，

两个要点，1是master 开启bin-log，2是将server-id 的配置。

配置master：

1.修改主服务器的配置文件/etc/mysql/my.cnf
vim /etc/mysql/my.cnf
把下面2行注释去掉
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log

2.查看master最新的bin-log

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 |      106 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.03 sec)

3. 修改slave 配置文件

1.将 server-id = 1修改为 server-id = 10，并确保这个ID没有被别的MySQL服务所使用。
2.增加以下几行
master_host=192.168.15.131
master_user=yangyi
master_password=123456
master_port=3306

4. 重启master和slave的MySQL数据库

sudo restart mysql 切记用这个，不用sudo /etc/init.d/mysql restart 因为出现不能完全重启的效果。

5. 查看一下slave的同步情况

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.15.131
                  Master_User: yangyi
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 106
               Relay_Log_File: yangyi-relay-bin.000007
                Relay_Log_Pos: 251
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

Slave_IO_Running: Yes
Slave_SQL_Running: Yes 说明同步成功。

验证一下同步：

去maste 插入一条数据，看能不能同步过来：

mysql> insert into test.t1 values(11),(12),(13);
Query OK, 3 rows affected (0.33 sec)
Records: 3  Duplicates: 0  Warnings: 0

去slave 看一下：

mysql> select * from test.t1;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    5 |
|    4 |
|    6 |
|    7 |
|    8 |
|    9 |
|   10 |
|   11 |
|   11 |
|   12 |
|   13 |
+------+
14 rows in set (0.00 sec)

成功同步过来，恭喜！！！！！！！！！！！！！！！！！！

看一下show slave status

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.15.131
                  Master_User: yangyi
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 205
               Relay_Log_File: yangyi-relay-bin.000007
                Relay_Log_Pos: 350
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

恩。bin-log 日志都同步的杠杠的。可喜可贺啊。。。

搞定。累死了。去睡觉了。

domorejojo

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL 用户授权和bin-log日志和主从复制、读写分离（2）

前言：前面一篇，我们ok了Ubuntu上Lamp的搭建以及MySQL·的用户授权和简单的bin-log日志的了解，这一篇，我们继续上面的，然后把主从复制完成。1. Mysql数据库的数据备份、恢复以及bin-log恢复。在MySQL中备份数据是一项非常重要的事情，因为不知道什么时候数据崩溃或者其他的原因导致数据丢失。备份的方法有很多。最常见的就是mysqldump命令了。一
复制链接

扫一扫