PHP开发笔记 - Powered by Discuz! Board

标题: mysql的复制 [打印本页]

作者: fangzhen 时间: 2011-5-20 16:12 标题: mysql的复制

--------一般数据库默认的空用户（匿名），可以对information_schema和test库拥有权限，删
mysql> use mysql
Database changed
mysql> delete from user where user='';
Query OK, 2 rows affected (0.01 sec)
mysql> drop user [url=]''@'%'[/url];
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
--------------------------------------------
mysql的复制，如果在/etc/my.cnf下开启bin-log
log-bin=/usr/local/mysql/log/mysql-bin.log需要指定log目录具有mysql组的读写权限
一般指定log-bin=mysql-bin即可
-----
主服务器服务启动后：
mysql>grant replication slave,replication client on *.* to [url=]'repl'@'%'[/url] identified by '123';
以下命令同上。
mysql> grant replication slave on *.* to [url=]'repl'@'192.168.0.38'[/url] identified by '123456';
修改/etc/my.cnf
log-bin=mysql-bin
server-id=1
如果是innodb表，还要开启相关的选项

从服务器上：
修改/etc/my.cnf
log-bin=mysql-bin
server-id=2
master-host    = 192.168.0.28
master-user    = repl
master-password = 123456
如果从服务器以后也做为主或切换到主服务器，也应该开启 bin-log
主服务器上

1.bmp (154.93 KB)

2011-5-20 16:12

从服务器可以看到：
mysql> show slave status\G
*************************** 1. row ***************************
            Slave_IO_State: Waiting for master to send event
               Master_Host: 192.168.0.28
               Master_User: repl
               Master_Port: 3306
            Connect_Retry: 60
            Master_Log_File: mysql-bin.000013
      Read_Master_Log_Pos: 106
            Relay_Log_File: slave38-relay-bin.000009
            Relay_Log_Pos: 251
      Relay_Master_Log_File: mysql-bin.000013
         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: 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
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)
mysql> select master_pos_wait('mysql-bin.000015','1595');
+--------------------------------------------+
| master_pos_wait('mysql-bin.000015','1595') |
+--------------------------------------------+
|                                        0 |
+--------------------------------------------+
1 row in set (0.00 sec)

--skip-slave-start
如果在从服务器执行

4.bmp (39.92 KB)

2011-5-20 16:15

就是说不执行启动从服务器的操作。
mysql> show processlist \G;
*************************** 1. row ***************************
   Id: 1
User: root
Host: localhost
   db: NULL
Command: Query
Time: 0
State: NULL
Info: show processlist
1 row in set (0.00 sec)

5.bmp (304.62 KB)

2011-5-20 16:15

用以下命令即可
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
--read-only选项
mysql> grant all privileges on test1.* to [url=]'z1'@'%'[/url] identified by '1234';
Query OK, 0 rows affected (0.01 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from db where user='z1' \G;
*************************** 1. row ***************************
               Host: %
               Db: test1
               User: z1
      Select_priv: Y
      Insert_priv: Y
      Update_priv: Y
      Delete_priv: Y
      Create_priv: Y
         Drop_priv: Y
         Grant_priv: N
   References_priv: Y
         Index_priv: Y
         Alter_priv: Y
Create_tmp_table_priv: Y
   Lock_tables_priv: Y
   Create_view_priv: Y
   Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: Y
      Execute_priv: Y
         Event_priv: Y
      Trigger_priv: Y
1 row in set (0.00 sec)
[root@slave38 mysql]# mysql -uz1 -p1234
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.1.41-log MySQL Community Server (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> show databases;
+--------------------+
| Database          |
+--------------------+
| information_schema |
| test1             |
+--------------------+
mysql> select * from t1;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
+------+
3 rows in set (0.00 sec)
mysql> insert into t1 values(4),(5);
ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement
-------------
选项replicate-do-db,replicate-do-table等，以下以replicate-do-table为例
只更新 test1.t1表
[root@slave38 mysql]# mysqld_safe --replicate-do-table=test1.t1 &
主服务器上
mysql> insert into t1 values(10),(20);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> insert into t2 values(1,'xiaofeng'),(2,'azhu');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from t2;
+------+----------+
| id | name    |
+------+----------+
| 1 | xiaofeng |
| 2 | azhu    |
+------+----------+
2 rows in set (0.00 sec)
mysql> select * from t1;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 10 |
| 20 |
+------+
5 rows in set (0.00 sec)
mysql> select * from t2;
Empty set (0.00 sec)
选项slave-skip-errors

图片附件: 1.bmp (2011-5-20 16:12, 154.93 KB) / 该附件被下载次数 108
http://phpvi.com/attachment.php?aid=232&k=4e65c230447fa60cf5ed82ab51eabe98&t=1732657048

图片附件: 2.bmp (2011-5-20 16:12, 472.72 KB) / 该附件被下载次数 118
http://phpvi.com/attachment.php?aid=233&k=b1cc3bbdfb6279f56e1090a0b429c018&t=1732657048

图片附件: 3.bmp (2011-5-20 16:15, 732.37 KB) / 该附件被下载次数 84
http://phpvi.com/attachment.php?aid=234&k=a1a33cd17961d76ab71bc1d7a1523aac&t=1732657048

图片附件: 4.bmp (2011-5-20 16:15, 39.92 KB) / 该附件被下载次数 78
http://phpvi.com/attachment.php?aid=235&k=13c7772aa401cb525df5e3794d59bc3b&t=1732657048

图片附件: 5.bmp (2011-5-20 16:15, 304.62 KB) / 该附件被下载次数 86
http://phpvi.com/attachment.php?aid=236&k=300d86ac205b3d6b783529172f115c9a&t=1732657048

欢迎光临 PHP开发笔记 (http://phpvi.com/)