mysql主从复制配置实现及其监控与维护

超、凢脫俗 2022-06-05 11:39 150阅读 0赞

**一、主从复制工作架构：**

** 1、特点： **

从服务器：有且只能有一个主服务器；

主服务器：可以有多从；

异步：从服务器的数据可能会落后于主服务器；

主服务器进行写操作，从服务器只进行读操作；

** 2、提高缓存命中率的解决思路：**

(1)、在程序中的解决：使用取模算法、使用一致性哈希算法

(2)、使用公共缓存：memcached

**3、主从复制架构mysql版本要求：**

(1)、双版本一致：从节点版本低于主节点可能会引起错误；

** 4、复制起点：**

(1)、从0开始：适用主从均为新建立的服务器；

(2)、主服务器已经运行一段时间且存不小的数据量：

1)、完全备份主服务上的数据，并将数据恢复至从服务器；

2)、从服务器从备份时主服务器二进制日志所在位置二始服务；

**二、主从配置过程：**

**1、主节点配置过程：master**

(1)、启用二进制日志，

\[mysqld\]

log\_bin=master-bin

\#binlog dump: 将从服务的io thread发出读取二进制日志事件的请求对应的数据发送给对方

(2)、为当前节点设置一个惟一的server-id

\[mysqld\]

server\_id=\#

(3)、创建有复制权限的用户账号；

REPLICATION SLAVE,REPLICATION CLIENT

GRANT REPLICATION SLAVE,REPLICATION CLIENTON \*.\* TO 'repluser'@'HOST' IDENTIFIED BY 'replpass';

mysql> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON \*.\* TO repluser@'172.16.%.%' IDENTIFIED BY 'replp@ss';

Query OK, 0 rows affected (0.03 sec)

mysql> FLUSH PRIVILEGES;

Query OK, 0 rows affected (0.00 sec)

** 2、从节点配置过程：slave:**

(1)、启用中继日志，并闭二进制日志（建议）

\[mysqld\]

\#log\_bin=slave-bin

relay\_log=relay-log

relay\_log\_index=relay\_log.index

(2)、为当前节点设置一个惟一的server-id

\[mysqld\]

server\_id=\#

(3)、使用有复制权限的用户账号连接至主服务器，并启动复制线程；

连接master:

mysql>HELP CHANGE MASTER TO

Name: 'CHANGE MASTER TO'

Description:

Syntax:

CHANGE MASTER TO option \[, option\]...

option:

MASTER\_BIND = 'interface\_name'

| MASTER\_HOST = 'host\_name'    \# 指明要连接的主节点，值类型字串

| MASTER\_USER = 'user\_name'    \# 具有复制权限的账号，值类型为字串

| MASTER\_PASSWORD = 'password' \# 上述用户的密码，值类型为字串

| MASTER\_PORT = port\_num

| MASTER\_CONNECT\_RETRY = interval

| MASTER\_HEARTBEAT\_PERIOD = interval

| MASTER\_LOG\_FILE = 'master\_log\_name'  \# 复制起点，主节点上二进制日志，值类型为字串

| MASTER\_LOG\_POS = master\_log\_pos      \# 复制起点，主节点上二进制日志中起始事件的位置，值类型为数值

| RELAY\_LOG\_FILE = 'relay\_log\_name'

| RELAY\_LOG\_POS = relay\_log\_pos

| MASTER\_SSL = \{0|1\}

| MASTER\_SSL\_CA = 'ca\_file\_name'

| MASTER\_SSL\_CAPATH = 'ca\_directory\_name'

| MASTER\_SSL\_CERT = 'cert\_file\_name'

| MASTER\_SSL\_KEY = 'key\_file\_name'

| MASTER\_SSL\_CIPHER = 'cipher\_list'

| MASTER\_SSL\_VERIFY\_SERVER\_CERT = \{0|1\}

| IGNORE\_SERVER\_IDS = (server\_id\_list)

mysql> CHANGE MASTER TOMASTER\_HOST='192.168.88.131',MASTER\_USER='repluser',MASTER\_PASSWORD='testpass',MASTER\_LOG\_FILE='master-bin.000003',MASTER\_LOG\_POS=358;

(4)启动io thread以及sql thread

IOthread：向master请求二进制日志中的事件

SQLthread：从中继日志中读取事件并在本地执行

mysql> start slave;

Query OK, 0 rows affected (0.00 sec)

**3、查看从节点的状态信息**

mysql> SHOW SLAVE STATUS\\G

**三、配置过程实例演示：**

环境：mysql主服务器：192.168.88.131

mysql从服务器：192.168.88.130

**1、主服务器配置：**

\[root@wwwmysql\]\# service mysqld stop

停止 mysqld： \[确定\]

\[root@wwwmysql\]\# rm -rf /var/lib/mysql/\*

\[root@wwwmysql\]\# \[root@www mysql\]\# service mysqld stop

停止 mysqld： \[确定\]

\[root@wwwmysql\]\# vim /etc/my.cnf

\[mysqld\]

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

user=mysql

\# Disablingsymbolic-links is recommended to prevent assorted security risks

symbolic-links=0

log\_bin=master-bin

\#启用二进制日志

\#relay\_log=relay-log

server-id=1

\#为当前节点设置一个惟一的server-id

innodb\_file\_per\_table=1

skip\_name\_resolve

\#auto\_increment\_offset=1

\#auto\_increment\_increment=2

default-storage-engine=InnoDB

\[mysqld\_safe\]

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

\[root@wwwmysql\]\# service mysqld restart

停止 mysqld： \[确定\]

初始化 MySQL 数据库： Installing MySQL system tables...

Filling helptables...

To start mysqldat boot time you have to copy

support-files/mysql.serverto the right place for your system

PLEASE REMEMBERTO SET A PASSWORD FOR THE MySQL root USER !

To do so, startthe server, then issue the following commands:

/usr/bin/mysqladmin-u root password 'new-password'

/usr/bin/mysqladmin-u root -h www.field.com password 'new-password'

Alternativelyyou can run:

/usr/bin/mysql\_secure\_installation

which will alsogive you the option of removing the test

databases andanonymous user created by default.  Thisis

stronglyrecommended for production servers.

See the manualfor more instructions.

You can startthe MySQL daemon with:

cd /usr ;/usr/bin/mysqld\_safe &

You can test theMySQL daemon with mysql-test-run.pl

cd /usr/mysql-test; perl mysql-test-run.pl

Please reportany problems with the /usr/bin/mysqlbug script!

\[确定\]

正在启动 mysqld： \[确定\]

\[root@wwwmysql\]\# mysql

Welcome to theMySQL monitor.  Commands end with ; or\\g.

Your MySQLconnection id is 4

Server version: 5.1.73-logSource distribution

Oracle is aregistered 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> showglobal VARIABLES LIKE  '%log%';

\+-----------------------------------------+---------------------------------+

|Variable\_name                           |Value                           |

\+-----------------------------------------+---------------------------------+

| back\_log                                   |50                              |

|binlog\_cache\_size                       |32768                           |

|binlog\_direct\_non\_transactional\_updates | OFF                             |

|binlog\_format                            |STATEMENT                       |

|expire\_logs\_days                        |0                               |

| general\_log                                |OFF                             |

|general\_log\_file                         |/var/run/mysqld/mysqld.log      |

|innodb\_flush\_log\_at\_trx\_commit          |1                               |

|innodb\_locks\_unsafe\_for\_binlog          |OFF                             |

|innodb\_log\_buffer\_size                  |1048576                         |

|innodb\_log\_file\_size                    |5242880                         |

|innodb\_log\_files\_in\_group               |2                               |

|innodb\_log\_group\_home\_dir               |./                              |

|innodb\_mirrored\_log\_groups              |1                               |

| log                                     | OFF                             |

| log\_bin                                | ON                              |

|log\_bin\_trust\_function\_creators         |OFF                             |

|log\_bin\_trust\_routine\_creators          |OFF                             |

| log\_error                               |/var/log/mysqld.log             |

|log\_output                              |FILE                            |

|log\_queries\_not\_using\_indexes           |OFF                             |

|log\_slave\_updates                       | OFF                             |

|log\_slow\_queries                        |OFF                             |

|log\_warnings                            |1                               |

|max\_binlog\_cache\_size                   |18446744073709547520            |

|max\_binlog\_size                         |1073741824                      |

|max\_relay\_log\_size                      |0                               |

| relay\_log                               |                                 |

|relay\_log\_index                        |                                |

|relay\_log\_info\_file                     |relay-log.info                  |

|relay\_log\_purge                         |ON                              |

|relay\_log\_space\_limit                   |0                               |

|slow\_query\_log                          |OFF                             |

|slow\_query\_log\_file                     |/var/run/mysqld/mysqld-slow.log |

|sql\_log\_bin                             |ON                              |

|sql\_log\_off                             |OFF                             |

|sql\_log\_update                          |ON                              |

|sync\_binlog                             |0                               |

\+-----------------------------------------+---------------------------------+

38 rows in set(0.02 sec)

mysql> showvariables like '%skip\_name\_resolve%' ;

\+-------------------+-----------+

|Variable\_name     | Value |

\+-------------------+-----------+

|skip\_name\_resolve | ON    |

\+-------------------+------------+

1 row in set(0.00 sec)

mysql> showdatabases;

\+--------------------------+

| Database                 |

\+--------------------------+

|information\_schema |

| mysql                     |

| test                        |

\+--------------------------+

3 rows in set(0.00 sec)

mysql> showglobal variables like '%server%';

\+----------------------+-------------------+

|Variable\_name        | Value             |

\+----------------------+-------------------+

|character\_set\_server | latin1             |

|collation\_server     | latin1\_swedish\_ci|

| server\_id           | 1                 |

\+----------------------+-------------------+

3 rows in set(0.00 sec)

\#创建有复制权限的用户账号

mysql> GRANTREPLICATION SLAVE,REPLICATION CLIENT ON \*.\* TO 'repluser'@'192.168.%.%'IDENTIFIED BY 'testpass';

Query OK, 0 rowsaffected (0.03 sec)

\#刷新权限

mysql> FLUSH PRIVILEGES;

Query OK, 0 rowsaffected (0.00 sec)

mysql>

mysql> showmaster status;

\+-------------------+----------+--------------+------------------+

\+-------------------+----------+--------------+------------------+

| master-bin.000003 |     358 |              |                  |

\+-------------------+----------+--------------+------------------+

1 row in set(0.00 sec)

**2、从服务器配置**

\[root@testmysql\]\# service mysqld stop

停止 mysqld： \[确定\]

\[root@testmysql\]\# rm -rf /var/lib/mysql/\*

\[root@testmysql\]\# vim /etc/my.cnf

\[mysqld\]

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

user=mysql

\# Disablingsymbolic-links is recommended to prevent assorted security risks

symbolic-links=0

\#log\_bin=master-bin建议关闭

relay\_log=relay-log

relay\_log\_index=relay\_log.index

\#启用中继日志，并闭二进制日志

server-id=7

\#为当前节点设置一个惟一的server-id

innodb\_file\_per\_table=1

skip\_name\_resolve

\#笔者所用版本为centos6默认的mysql5.1，该选项只需添加即可启动，无需设置值，此外，设置值会报错无法启动

\#auto\_increment\_offset=2

\#auto\_increment\_increment=2

default-storage-engine=InnoDB

\[mysqld\_safe\]

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

\[root@testmysql\]\# service mysqld restart

停止 mysqld： \[确定\]

初始化 MySQL 数据库： Installing MySQL system tables...

Filling helptables...

To start mysqldat boot time you have to copy

support-files/mysql.serverto the right place for your system

PLEASE REMEMBERTO SET A PASSWORD FOR THE MySQL root USER !

To do so, startthe server, then issue the following commands:

/usr/bin/mysqladmin-u root password 'new-password'

/usr/bin/mysqladmin-u root -h test.field.com password 'new-password'

Alternativelyyou can run:

/usr/bin/mysql\_secure\_installation

which will alsogive you the option of removing the test

databases andanonymous user created by default.  Thisis

stronglyrecommended for production servers.

See the manualfor more instructions.

You can startthe MySQL daemon with:

cd /usr ;/usr/bin/mysqld\_safe &

You can test theMySQL daemon with mysql-test-run.pl

cd/usr/mysql-test ; perl mysql-test-run.pl

Please reportany problems with the /usr/bin/mysqlbug script!\[确定\]

正在启动 mysqld： \[确定\]

\[root@testmysql\]\# mysql

Welcome to theMySQL monitor.  Commands end with ; or\\g.

Your MySQLconnection id is 2

Server version:5.1.73 Source distribution

Oracle is aregistered 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> showvariables like '%log%';

\+-----------------------------------------+---------------------------------+

|Variable\_name                           |Value                           |

\+-----------------------------------------+---------------------------------+

| back\_log                                | 50                              |

|binlog\_cache\_size                       |32768                           |

|binlog\_direct\_non\_transactional\_updates | OFF                             |

|binlog\_format                           |STATEMENT                       |

|expire\_logs\_days                        |0                               |

|general\_log                             |OFF                             |

|general\_log\_file                        |/var/run/mysqld/mysqld.log      |

|innodb\_flush\_log\_at\_trx\_commit          |1                               |

|innodb\_locks\_unsafe\_for\_binlog          |OFF                             |

| innodb\_log\_buffer\_size                  | 1048576                         |

|innodb\_log\_file\_size                    |5242880                         |

|innodb\_log\_files\_in\_group               |2                               |

|innodb\_log\_group\_home\_dir               |./                              |

|innodb\_mirrored\_log\_groups              |1                               |

| log                                     | OFF                             |

| log\_bin                                | OFF                             |

|log\_bin\_trust\_function\_creators         |OFF                             |

|log\_bin\_trust\_routine\_creators          |OFF                             |

| log\_error                               |/var/log/mysqld.log             |

|log\_output                              |FILE                            |

|log\_queries\_not\_using\_indexes           |OFF                             |

|log\_slave\_updates                       |OFF                             |

| log\_slow\_queries                        | OFF                             |

|log\_warnings                            |1                               |

|max\_binlog\_cache\_size                   |18446744073709547520            |

|max\_binlog\_size                         | 1073741824                      |

|max\_relay\_log\_size                      |0                               |

| relay\_log                              | relay-log                       |

| relay\_log\_index                        | relay\_log.index                 |

| relay\_log\_info\_file                    | relay-log.info                  |

|relay\_log\_purge                         |ON                              |

|relay\_log\_space\_limit                   |0                               |

|slow\_query\_log                          |OFF                             |

|slow\_query\_log\_file                     |/var/run/mysqld/mysqld-slow.log |

|sql\_log\_bin                             |ON                              |

|sql\_log\_off                             | OFF                             |

|sql\_log\_update                          |ON                              |

|sync\_binlog                             |0                               |

\+-----------------------------------------+---------------------------------+

38 rows in set(0.00 sec)

mysql> showvariables like '%skip\_name\_resolve%' ;

\+-------------------+------------+

|Variable\_name     | Value  |

\+-------------------+------------+

| skip\_name\_resolve | ON    |

\+-------------------+------------+

1 row in set(0.00 sec)

\#可以看到，添加即可启动

mysql> showglobal variables like '%server%';

\+----------------------+-----------------------+

|Variable\_name        | Value                |

\+----------------------+-----------------------+

|character\_set\_server | latin1              |

|collation\_server     | latin1\_swedish\_ci|

| server\_id           | 7                          |

\+----------------------+-----------------------+

3 rows in set(0.00 sec)

\#使用有复制权限的用户账号连接至主服务器

mysql>CHANGE MASTER TOMASTER\_HOST='192.168.88.131',MASTER\_USER='repluser',MASTER\_PASSWORD='testpass',MASTER\_LOG\_FILE='master-bin.000003',MASTER\_LOG\_POS=358;

Query OK, 0 rowsaffected (0.03 sec)

\#查看从服务启状态

mysql> SHOW  SLAVE  STATUS\\G

\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

Slave\_IO\_State:

Master\_Host: 192.168.88.131

Master\_User: repluser

Master\_Port: 3306

Connect\_Retry: 60

Master\_Log\_File: master-bin.000003

Read\_Master\_Log\_Pos: 358

Relay\_Log\_File: relay-log.000001

Relay\_Log\_Pos: 4

Relay\_Master\_Log\_File:master-bin.000003

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: 358

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)

\#\#\#\#并启动复制线程

mysql> startslave;

Query OK, 0 rowsaffected (0.00 sec)

mysql> SHOWSLAVE STATUS\\G

\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

Slave\_IO\_State: Waiting formaster to send event

Master\_Host: 192.168.88.131

Master\_User: repluser

Master\_Port: 3306

Connect\_Retry: 60

Master\_Log\_File: master-bin.000003

Read\_Master\_Log\_Pos: 358

Relay\_Log\_File: relay-log.000002

Relay\_Log\_Pos: 252

Relay\_Master\_Log\_File: master-bin.000003

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: 358

Relay\_Log\_Space: 401

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>

**3、测试主从同步：**

主服务器：

mysql> showdatabases;

\+--------------------+

| Database          |

\+--------------------+

|information\_schema |

| mysql               |

| test                  |

\+--------------------+

3 rows in set(0.00 sec)

mysql>CREATE DATABASE testslave;

Query OK, 1 rowaffected (0.00 sec)

mysql> showdatabases;

\+--------------------+

| Database          |

\+--------------------+

|information\_schema |

| mysql              |

| test                 |

| testslave          |

\+--------------------+

4 rows in set(0.00 sec)

mysql> showmaster status;

\+-------------------+----------+--------------+------------------+

\+-------------------+----------+--------------+------------------+

| master-bin.000003 |     451 |              |                    |

\+-------------------+----------+--------------+------------------+

1 row in set(0.00 sec)

mysql>

从服务器：

mysql> SHOWSLAVE STATUS\\G

\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

Slave\_IO\_State: Waiting formaster to send event

Master\_Host: 192.168.88.131

Master\_User: repluser

Master\_Port: 3306

Connect\_Retry: 60

Master\_Log\_File: master-bin.000003

Read\_Master\_Log\_Pos: 451

\#确认masterstatus是否一致

Relay\_Log\_File: relay-log.000002

Relay\_Log\_Pos: 345

Relay\_Master\_Log\_File:master-bin.000003

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:451

Relay\_Log\_Space: 494

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> showdatabases;

\+--------------------+

| Database          |

\+--------------------+

|information\_schema |

| mysql              |

| test                 |

| testslave          |

\+--------------------+

4 rows in set(0.01 sec)

mysql>

**主服务器：**

mysql> usetestslave

Database changed

mysql> showtables;

Empty set (0.00sec)

mysql>CREATE TABLE id(id int);

Query OK, 0 rowsaffected (0.08 sec)

mysql>insert into id values (666),(13888);

Query OK, 2 rowsaffected (0.00 sec)

Records: 2  Duplicates: 0 Warnings: 0

mysql>select \* from id;

\+--------+

| id       |

\+--------+

|   666  |

| 13888 |

\+--------+

2 rows in set(0.00 sec)

mysql> showmaster status;

\+-------------------+----------+--------------+------------------+

\+-------------------+----------+--------------+------------------+

| master-bin.000003 |     745 |              |                  |

\+-------------------+----------+--------------+------------------+

1 row in set(0.00 sec)

mysql>

**从服务器：**

mysql> usetestslave

Reading tableinformation for completion of table and column names

You can turn offthis feature to get a quicker startup with -A

Database changed

mysql> showtables;

\+-------------------------+

|Tables\_in\_testslave |

\+-------------------------+

| id                          |

\+-------------------------+

1 row in set(0.00 sec)

mysql>select \* from id;

\+-------+

| id      |

\+-------+

|   666 |

| 13888 |

\+-------+

2 rows in set(0.00 sec)

mysql> SHOWSLAVE STATUS\\G;

\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

Slave\_IO\_State: Waiting formaster to send event

Master\_Host: 192.168.88.131

Master\_User: repluser

Master\_Port: 3306

Connect\_Retry: 60

Master\_Log\_File: master-bin.000003

Read\_Master\_Log\_Pos: 745

Relay\_Log\_File: relay-log.000002

Relay\_Log\_Pos: 639

Relay\_Master\_Log\_File:master-bin.000003

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: 745

Relay\_Log\_Space: 788

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

\#出现此错误是sql不合法原因：\\G后面不需要再加分号;

mysql> SHOWSLAVE STATUS\\G

\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

Slave\_IO\_State: Waiting formaster to send event

Master\_Host: 192.168.88.131

Master\_User: repluser

Master\_Port: 3306

Connect\_Retry: 60

Master\_Log\_File:master-bin.000003

Read\_Master\_Log\_Pos: 745

Relay\_Log\_File: relay-log.000002

Relay\_Log\_Pos: 639

Relay\_Master\_Log\_File:master-bin.000003

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: 745

Relay\_Log\_Space: 788

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)

**四、复制的管理与维护：**

**1、监控复制**

SHOW MASTER STATUS

SHOW BINLOG EVENTS

SHOW BINARY LOGS

SHOW SLAVE STATUS

**2、slave是否发了复制延迟**

mysql> SHOW SLAVE STATUS\\G查看以下项：

Seconds\_Behind\_Master: 0

**3、确定master/slave节点数据是否一致**

(1)表自身的checksum

mysql> help checksum table

(2)使用percona-tools中的pt-table-checksum

**4、数据不一致时的修改方法**

重复设定复制机制

使用mysqldump从master导出slave不同的数据

参考mysql备份和恢复

**5、为从库设定新的主库**

在从库停止复制线程，而后重新设定CHANGE MASTER TO命令即可；

mysql> stop slave;

Query OK, 0 rows affected (0.10 sec)

mysql> GRANT REPLICATION SLAVE,REPLICATIONCLIENT ON \*.\* TO 'repluser'@'HOST' IDENTIFIED BY 'REPLPASS';

**6、跟复制功能相关的文件：**

master.info：保存slave连接master时所需要信息；纯文本文件；

relay-log.info：保存了当前slave节点上（主节点上的）二进制日志和当前节点中继日志的对应关系；

\[root@test mysql\]\# cat master.info

master-bin.000003

745

192.168.88.131

repluser

testpass

3306

\[root@test mysql\]\# cat relay-log.info

./relay-log.000003

252

master-bin.000003

745

**7、清理二进制日志：PURGE命令**

实例：

mysql> show binary logs;

+-------------------+-----------------+

|Log\_name          | File\_size     |

+-------------------+-----------------+

|master-bin.000001 |     19924 |

|master-bin.000002 |    775387 |

|master-bin.000003 |       387   |

|master-bin.000004 |       527   |

+-------------------+-----------------+

4rows in set (0.00 sec)

mysql>purge binary logs to 'master-bin.000002';

QueryOK, 0 rows affected (0.08 sec)

mysql>show binary logs;

+-------------------+-----------------+

|Log\_name          | File\_size     |

+-------------------+-----------------+

|master-bin.000002 |    775387 |

|master-bin.000003 |       387   |

|master-bin.000004 |       527   |

+-------------------+-----------------+

3rows in set (0.00 sec)

mysql>quit

Bye

\[root@testmysql\]\# ls

ibdata1      master-bin.000002  master-bin.index  mysql             relay-log.000006  test ib\_logfile0  master-bin.000003  master.info       mysql.sock        relay-log.index ib\_logfile1  master-bin.000004  mydb   relay-log.000005  relay-log.info

\[root@test mysql\]\# catmaster-bin.index

./master-bin.000002

./master-bin.000003

./master-bin.000004

**8、提升从服务器为主服务器**

计划内提升一个从库为主库：

(1) 停止向老的主库写入数据；

(2) 让计划提升为主库的从库赶上主库；

(3) 提升从库为主库

(4) 修改其它从库的指向

计划外提升一个从库为主库：

(1) 确定哪个从库的数据为最新最全；