mysql主从同步

最新推荐文章于 2024-07-05 17:16:11 发布
最新推荐文章于 2024-07-05 17:16:11 发布
阅读量2.6k 收藏 1
点赞数
分类专栏： 工具配置 数据库相关 文章标签： 数据库 服务器
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/xluren/article/details/10144165
版权

1.如何允许登录远程的mysql server

GRANT ALL PRIVILEGES ON *.* TO 'hello'@'192.168.74.227'IDENTIFIED BY 'hello' WITH GRANT OPTION; 
flush privileges;
注：这时候如果有可能登录失败的话，看下防火墙

mysql> grant 权限1,权限2,…权限n on 数据库名称.表名称 to 用户名@用户地址 identified by ‘连接口令’;
权限1,权限2,…权限n代表select,insert,update,delete,create,drop,index,alter,grant,references,reload,shutdown,process,file等14个权限。
当权限1,权限2,…权限n被all privileges或者all代替，表示赋予用户全部权限。
当数据库名称.表名称被*.*代替，表示赋予用户操作服务器上所有数据库所有表的权限。
用户地址可以是localhost，也可以是ip地址、机器名字、域名。也可以用’%'表示从任何地址连接。
‘连接口令’不能为空，否则创建失败。

2.如何配置mysql的master&slave

准备工作，有两个linux主机

master:192.168.74.225 centos6.4 32位 mysql5.5

slave:192.168.74.227  centos6.4 32位 mysql5.5

master 的用户名是root，无密码

slave   的用户名是root，无密码

slave登录master进行同步的账号是rep

1.允许slave登录master

GRANT ALL PRIVILEGES ON *.* TO 'hello'@'192.168.74.227'IDENTIFIED BY 'hello' WITH GRANT OPTION; 
flush privileges;
这样就可以登录了，如果登录失败，请关闭防火墙 

mysql> select host,user from user;
+----------------+-------+
| host           | user  |
+----------------+-------+
| 127.0.0.1      | root  |
| 192.168.74.227 | hello |
| 192.168.74.227 | rep   |
| ::1            | root  |
| Master.Mysql   |       |
| Master.Mysql   | root  |
| localhost      |       |
| localhost      | root  |
+----------------+-------+
8 rows in set (0.00 sec)
| 192.168.74.227 | hello |
开始是没有的执行完那个语句之后，这个就出现了。

2.配置master的my.cnf

[mysqld]
#master configure
server-id = 1
log-bin=mysql-bin
binlog-do-db=master
binlog-ignore-db=mysql
#master configure
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

maser的id应该是1，说明log目录，同步的数据库，不允许同步的数据库

然后在Master上增加一个账号专门用于同步，如下：
GRANT REPLICATION SLAVE ON *.* TO rep@192.168.74.227 IDENTIFIED BY 'hello';
如果想要在Slave上有权限执行"LOAD TABLE FROM MASTER" 或 "LOAD DATA FROM MASTER"语句的话，必须授予全局的 FILE 和 SELECT 权限：
GRANT FILE,SELECT,REPLICATION SLAVE ON *.* TO rep@192.168.74.227 IDENTIFIED BY 'hello';
接下来备份Master上的数据，首先执行如下SQL语句：
FLUSH TABLES WITH READ LOCK;
然后把同步的数据tar打包，然后scp到227那台机器上，解压，注意权限问题
整个步骤完成之后，执行
UNLOCK TABLES

3.配置slave

[mysqld]
#configure master-slave
server-id=2
#master-host=192.168.74.225
#master-user=rep
#master-password=hello
replicate-ignore-db=mysql
replicate-do-db=master
#configure master-slave

同步的数据库是master，不允许同步的是mysql
启动slave的数据库登录然后执行

接下来在Slave上检验一下是否能正确连接到Master上，并且具备相应的权限 
mysql -h192.168.74.225 -urep -phello
SHOW GRANTS;
效果如下： 

mysql> SHOW GRANTS;
+-------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for rep@192.168.74.227                                                                                                             |
+-------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, FILE, REPLICATION SLAVE ON *.* TO 'rep'@'192.168.74.227' IDENTIFIED BY PASSWORD '*6B4F89A54E2D27ECD7E8DA05B4AB8FD9D1D8B119' |
+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
启动数据库

执行这个语句

告诉slave主数据库一些信息

CHANGE MASTER TO MASTER_HOST='192.168.74.225',MASTER_USER='rep',MASTER_PASSWORD='hello',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=0;
4.返回到master主数据库 

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: 192.168.74.225
                  Master_User: rep
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 4
               Relay_Log_File: mysqld-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: No
            Slave_SQL_Running: No
              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: 4
              Relay_Log_Space: 107
              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: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 0
1 row in set (0.00 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: Connecting to master
                  Master_Host: 192.168.74.225
                  Master_User: rep
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 4
               Relay_Log_File: mysqld-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Connecting
            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: 4
              Relay_Log_Space: 107
              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: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 1045
                Last_IO_Error: error connecting to master 'rep@192.168.74.225:3306' - retry-time: 60  retries: 86400
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 0
1 row in set (0.00 sec)

这样应该就可以了

测试一下：

在master执行下面的语句：

mysql> use master;
Database changed
mysql>  CREATE TABLE stu(id VARCHAR(10), name VARCHAR(20));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into table stu(id,name) values(10,"xluren");
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 'table stu(id,name) values(10,"xluren")' at line 1
mysql> insert into stu(id,name) values(10,"xluren");
Query OK, 1 row affected (0.00 sec)

在slave测试一下： 

[root@Slave mysql]# mysql -h 127.0.0.1 -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.5.33 MySQL Community Server (GPL) by Remi

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.

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

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| master             |
| mysql              |
| test               |
| url                |
+--------------------+
5 rows in set (0.00 sec)

mysql> use maser;
ERROR 1049 (42000): Unknown database 'maser'
mysql> use master;
Database changed
mysql> show tables;
+------------------+
| Tables_in_master |
+------------------+
| stu              |
+------------------+
1 row in set (0.01 sec)

mysql> select * from stu;
+------+--------+
| id   | name   |
+------+--------+
| 10   | xluren |
+------+--------+
1 row in set (0.00 sec)

mysql>

整个的过程就是这样的

晚上再从新搭建一次，看看整个过程是否通顺。

再次测试了下，绝对的好用，添加一个slave的步骤是怎样的？

1.去master添加一条slave，

GRANT REPLICATION SLAVE ON *.* TO slave2@192.168.74.230 IDENTIFIED BY 'hello';

2.赋予各种权限

GRANT FILE,SELECT,REPLICATION SLAVE ON *.* TO slave2@192.168.74.230 IDENTIFIED BY 'hello';

3.锁定，禁止写

FLUSH TABLES WITH READ LOCK;

4.mysqldump 备份数据库

5.到230那台机器上，把数据库导进来

6.show master status;

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

同步的file是mysql-bin.000003

7.配置slave2的my.cnf

[mysqld]
#configure master-slave  
server-id=3
#master-host=192.168.74.225  
#master-user=rep  
#master-password=hello  
replicate-ignore-db=mysql  
replicate-do-db=master  
##configure master-slave

8.测试链接情况 

mysql -h 192.168.74.225 -u slave2 -phello
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 706
Server version: 5.5.33-log MySQL Community Server (GPL) by Remi

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.

mysql> show grants;
+----------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for slave2@192.168.74.230                                                                                                             |
+----------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, FILE, REPLICATION SLAVE ON *.* TO 'slave2'@'192.168.74.230' IDENTIFIED BY PASSWORD '*6B4F89A54E2D27ECD7E8DA05B4AB8FD9D1D8B119' |
+----------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

9.告诉slave2，同步的mysql server 

mysql> CHANGE MASTER TO MASTER_HOST='192.168.74.225',MASTER_USER='slave2',MASTER_PASSWORD='hello',MASTER_LOG_FILE='mysql-bin.000003',MASTER_LOG_POS=0; 
ERROR 1198 (HY000): This operation cannot be performed with a running slave; run STOP SLAVE first
mysql> stop Slave;
Query OK, 0 rows affected (0.04 sec)

mysql> CHANGE MASTER TO MASTER_HOST='192.168.74.225',MASTER_USER='slave2',MASTER_PASSWORD='hello',MASTER_LOG_FILE='mysql-bin.000003',MASTER_LOG_POS=0; 
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW SLAVE STATUS\G ;
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: 192.168.74.225
                  Master_User: slave2
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 4
               Relay_Log_File: mysqld-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: No
            Slave_SQL_Running: No
              Replicate_Do_DB: master
          Replicate_Ignore_DB: mysql
           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: 4
              Relay_Log_Space: 107
              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: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 1045
                Last_IO_Error: error connecting to master 'slave3@192.168.74.225:3306' - retry-time: 60  retries: 86400
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 0
1 row in set (0.00 sec)

ERROR: 
No query specified

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.74.225
                  Master_User: slave2
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 1563
               Relay_Log_File: mysqld-relay-bin.000002
                Relay_Log_Pos: 1709
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: master
          Replicate_Ignore_DB: mysql
           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: 1563
              Relay_Log_Space: 1866
              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
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
1 row in set (0.00 sec)

ERROR: 
No query specified

10.master解锁 

mysql> UNLOCK TABLES
    -> ;
Query OK, 0 rows affected (0.00 sec)
11.测试下

Master端

mysql> use master;
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> insert into stu(id,name) values(2015,"usa or google");
Query OK, 1 row affected (0.03 sec)

mysql>

12.slave1端验证 

mysql> select * from stu;
+------+--------+
| id   | name   |
+------+--------+
| 10   | xluren |
| 100  | hello  |
| 2013 | lurenx |
| 2014 | lurenx |
| 2014 | lurenx |
| 2014 | lurenx |
| 2014 | lurenx |
| 2014 | lurenx |
| 2014 | lurenx |
+------+--------+
9 rows in set (0.00 sec)

mysql> select * from stu;
+------+---------------+
| id   | name          |
+------+---------------+
| 10   | xluren        |
| 100  | hello         |
| 2013 | lurenx        |
| 2014 | lurenx        |
| 2014 | lurenx        |
| 2014 | lurenx        |
| 2014 | lurenx        |
| 2014 | lurenx        |
| 2014 | lurenx        |
| 2014 | lurenx        |
| 2015 | usa or google |
+------+---------------+
11 rows in set (0.00 sec)

mysql>

13.slave2验证下 

[root@Slave2 hello]# mysql -h 127.0.0.1 -uroot 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 17
Server version: 5.5.33 MySQL Community Server (GPL) by Remi

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.

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

mysql> use master;
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 stu;
+------+--------+
| id   | name   |
+------+--------+
| 10   | xluren |
| 100  | hello  |
| 2013 | lurenx |
| 2014 | lurenx |
| 2014 | lurenx |
| 2014 | lurenx |
| 2014 | lurenx |
| 2014 | lurenx |
| 2014 | lurenx |
| 2014 | lurenx |
+------+--------+
10 rows in set (0.01 sec)

mysql> select * from stu;
+------+---------------+
| id   | name          |
+------+---------------+
| 10   | xluren        |
| 100  | hello         |
| 2013 | lurenx        |
| 2014 | lurenx        |
| 2014 | lurenx        |
| 2014 | lurenx        |
| 2014 | lurenx        |
| 2014 | lurenx        |
| 2014 | lurenx        |
| 2014 | lurenx        |
| 2014 | lurenx        |
| 2014 | lurenx        |
| 2014 | lurenx        |
| 2014 | lurenx        |
| 2014 | lurenx        |
| 2014 | lurenx        |
| 2014 | lurenx        |
| 2015 | usa or google |
+------+---------------+
18 rows in set (0.00 sec)

mysql>

简单的总结

1.chkconfig关闭主从服务器的iptables

2.chkconfig设置mysqld开机自动运行

3.配置/etc/my.cnf

4.在主服务器上

mysql> GRANT REPLICATION SLAVE ON *.* TO slave@192.168.74.236 IDENTIFIED BY 'hello';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT FILE,SELECT,REPLICATION SLAVE  ON *.* TO slave@192.168.74.236 IDENTIFIED BY 'hello';
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.00 sec)

5.备份数据库信息，利用tar压缩，scp到从服务器上，利用tar解压，然后使用相同的用户名密码登录，完成数据的备份

6.在主服务器执行mysql> UNLOCK TABLES;  取消锁定，完成数据库的备份工作

7.重启主从服务器的数据库，然后执行

mysql> SHOW SLAVE STATUS\G
Empty set (0.00 sec)

mysql> SHOW MAster STATUS\G
*************************** 1. row ***************************
            File: mysql-bin.000001
        Position: 106
    Binlog_Do_DB: webdb
Binlog_Ignore_DB: mysql
1 row in set (0.00 sec)

8.在从服务器上执行 

[root@Slave mysql]# mysql -h 127.0.0.1 -u root -phello
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.69 Source distribution

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.

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

mysql> CHANGE MASTER TO MASTER_HOST='192.168.74.235',MASTER_USER='slave',MASTER_PASSWORD='hello',MASTER_LOG_FILE='mysql-bin.000001';
Query OK, 0 rows affected (0.02 sec)

mysql> show SLAVE status;
+----------------+----------------+-------------+-------------+---------------+------------------+---------------------+-------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+
| Slave_IO_State | Master_Host    | Master_User | Master_Port | Connect_Retry | Master_Log_File  | Read_Master_Log_Pos | Relay_Log_File          | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master | Master_SSL_Verify_Server_Cert | Last_IO_Errno | Last_IO_Error | Last_SQL_Errno | Last_SQL_Error |
+----------------+----------------+-------------+-------------+---------------+------------------+---------------------+-------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+
|                | 192.168.74.235 | slave       |        3306 |            60 | mysql-bin.000001 |                   4 | mysqld-relay-bin.000001 |             4 | mysql-bin.000001      | No               | No                | webdb           | mysql               |                    |                        |                         |                             |          0 |            |            0 |                   4 |             106 | None            |                |             0 | No                 |                    |                    |                 |                   |                |                  NULL | No                            |             0 |               |              0 |                |
+----------------+----------------+-------------+-------------+---------------+------------------+---------------------+-------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+
1 row in set (0.00 sec)

mysql> show SLAVE status\G;
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: 192.168.74.235
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 4
               Relay_Log_File: mysqld-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: No
            Slave_SQL_Running: No
              Replicate_Do_DB: webdb
          Replicate_Ignore_DB: mysql
           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: 4
              Relay_Log_Space: 106
              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: NULL
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)

ERROR: 
No query specified

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.74.235
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 106
               Relay_Log_File: mysqld-relay-bin.000002
                Relay_Log_Pos: 251
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: webdb
          Replicate_Ignore_DB: mysql
           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: 407
              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
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)

ERROR: 
No query specified

经过几次的配置，越来越明确方法，也越来越快了。




xluren
关注
专栏目录
MySQL主从同步各类型故障总结
Happy_zlh的博客
10-26 2537
主从同步故障总结 错误一 [ERROR] Slave I/O: error connecting to master 'replication@VMS00782:3306' - retry-time: 60 retries: 2, Error_code: 1045 错误原因：从库用来连接主库的用户权限或者密码不对 解决方法：首先在主库上检查用来主从复制的用户权限，如果没有问题在检查从库使用的密码是否正确。 错误二 140331 10:08:18 [ERROR] Error reading mas
Mysql主从同步的实现原理
09-09
MySQL主从同步是一种常见的数据库复制策略，用于提升系统的可扩展性、容错性和数据安全性。在主从同步架构中，数据库操作首先在主（master）服务器执行，然后将这些操作实时地复制到从（slave）服务器，确保数据的...
MySQL主从同步 1045
计算机毕业论文源码，学生个人网页制作html源码。贴近用户做网络推广和互联网优化。
11-17 186
请教mysql主从同步方式是否支持视图 请教mysql主从同步方式是否支持视图 --主机开两个窗口，一个进入mysql，一个是shell--主机阻断写操作mysql>FLUSHTABLESWITHREADLOCK;QueryOK,0rowsaffected(0.00sec)mysql>mysql>my...
mysql实现主从复制/主从同步
xdscode的博客
12-01 370
业务场景 小公司业务代码存于一个服务器上，而这个服务器有的时候回宕机，导致业务停顿，造成影响。这个时候 就需要做高可用 两个ngix+两个tomcat+两个mysql实现高可用，避免单点问题。中间使用keepalived监听。下面先从简单的mysql主从搞起。下面按照老方式，what->why->how ,是什么，为什么，怎么做来讲解一波。 一、（what）什么是mysql 的主从复制？ ① 简介 指一台服务器充当主数据库服务器，另一台或多台服务器充当从数据库服务器，主服务器中的数据自动复制到从
mysql主从复1049错误_【转】mysql ERROR 1049 (42000): Unknown database '******' ”错误处理办法...
weixin_35937871的博客
01-30 842
当初刚装MySQL的时候，到网上查的命令行登陆MySQL的方法都是mysql -u root -p password例如：mysql -r root -p 123456但是奇怪的是这条命令我输进去死活都不对，它都会要求再输入一遍密码，然后返回“ERROR 1049 (42000): Unknown database '123456'”这个错误提示很明显，我们-P后面的password代表的是数据库...
MYSQL出错代码及出错信息对照
pysche 没有声音的房间
05-29 1711
1005：创建表失败1006：创建数据库失败1007：数据库已存在，创建数据库失败1008：数据库不存在，删除数据库失败1009：不能删除数据库文件导致删除数据库失败1010：不能删除数据目录导致删除数据库失败1011：删除数据库文件失败1012：不能读取系统表中的记录1020：记录已被其他用户修改1021：硬盘剩余空间不足，请加大硬盘可用空间1022：关键字重复，更改记录失败1023：关闭时发生
12.1： MySQL主从同步 、 主从同步模式 、 总结和答疑.docx
03-05
MySQL主从同步配置和实现 MySQL主从同步是指将一个MySQL服务器的数据实时地复制到另一个或多个服务器上，以实现数据的高可用性和灾难恢复。这种方式可以实现数据的热备援、读写分离、负载均衡等功能。 1. 案例 1：...
MySQL主从同步配置过程.docx
03-19
MySQL 主从同步配置过程 一、 MySQL 主从同步配置概述 MySQL 主从同步配置是指将 MySQL 数据库的数据从一台服务器（主服务器）同步到另一台服务器（从服务器）的过程。这种配置可以实现数据的高可用性和灾难恢复，...
docker实现mysql主从复制【实战错误：1049】
最新发布
DDDM456的博客
07-05 272
问题产生原因：主数据库中提前创建了指定数据库，后配置的主从复制，导致从数据库在同步数据时报错找不到数据库，同步失败。在主数据库中查询：show master status;在从数据库中执行：stop slave；Position的值为1725。
mysql： Last_IO_Errno错误代码说明
yshir
05-04 200
1005：创建表失败 1006：创建数据库失败 1007：数据库已存在，创建数据库失败 1008：数据库不存在，删除数据库失败 1009：不能删除数据库文件导致删除数据库失败 1010：不能删除数据目录导致删除数据库失败 1011：删除数据库文件失败 1012：不能读取系统表中的记录 1020：记录已被其他用户修改 1021：硬盘剩余空间不足，请加大硬盘可用空间 1022：关键字重复，更改记录失...
mysql主从故障_MySQL主从故障修复
weixin_35019131的博客
01-19 354
测试库：192.168.1.2 主192.168.1.3 从192.168.1.4 主 4又是2的从库192.168.1.5 从有人修改了192.168.1.2和192.168.1.3的数据库参数后，重启数据库。忘记了192.168.1.4又是192.168.1.2的从库，导致192.168.1.2和192.168.1.4的主从断掉。并且在192.168.1.2上创建了新库还原数据删除等操作，导致...
Mysql主从复制报错Last_Errno: 1305
阿丽
10-22 6900
Mysql主从复制某些库表时总出现各种问题，之前根据业务配置的只张同步的表如下（作用同replicate_do_table）： replicate_wild_do_table=me.parana_brands replicate_wild_do_table=me.parana_items replicate_wild_do_table=me.parana_orders replicate_wil
MySQL出现：ERROR 1049 (42000): Unknown database 'XXX'解决方法
鄙人kunzhi96，感恩遇见！
04-11 7万+
问题 记得之前安装MySQL数据库的时候，就是通过命令行输入 mysql -u root -p password 今天在输入的时候却发现命令输入不正确，而且会要求再输入一遍密码，然后返回 “ERROR 1049 (42000): Unknown database '123'” 如图显示 这个错误提示是不知道123这个数据库，意思是-p后面的password代表的是数据库名称。但-p后面的参数...
MySQL同步故障：“ Slave_SQL_Running:No“ 自己的解决方法
sunnySe的博客
04-24 4645
想看解决方法的直接拉到最后即可，有方法总结。 1.当启动从服务器执行show slave status \G;发现Slave_SQL_Running:No竟然为NO，从show出来的信息中发现了一些错误提示： Last_SQL_Errno: 1007 Last_SQL_Error: Error 'Can't create database 'datalook'; database exists' ...
Linux下MySQL主从报错之 Last_SQL_Errno: 1062
Beta blog
05-24 1914
//报错示例 Last_SQL_Errno: 1062 Last_SQL_Error: Error 'Duplicate entry '3183' for key 'PRIMARY'' on query. Default database: //解决方法，跳过出差数据，如果只有一条数据出错且该数据不重要的话，可以使用 mysql> stop slave; mysql> set...
Mysql ERROR 1049 (42000): Unknown database '******' ”错误
tclwh123的博客
10-28 6037
mysql -u root -p root -p后面输入密码 -p和密码要连起来 mysql -h localhost -u root -ppassword 这样就对了
解决：mysql报错ERROR 1049 (42000): Unknown database ‘数据库‘
热门推荐
Along_168163的博客
03-21 17万+
MySQL问题解决
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值