mysql 备份 master_MySQL主(Master)从(Slave)备份；

Slave1:10.10.10.7

Slave2:10.10.10.8

1) MySQL 安装 (分别在Master,Slave1,Slave2 运行如下命令)

[root@Cent01 ~]# yum install mysql-server mysql -y

[root@Cent01 ~]# service mysqld start

2)配置my.cnf

[root@Cent01 ~]# mysqladmin -u root password 123.com  //修改mysql root的密码为"123.com"

[root@Cent01 ~]# vi /etc/my.cnf    //修改my.cfg 配置文件,添加如下内容；

相关信息如下：

#Master

[mysqld]

log-bin=MySQL-bin    //二进制文件

server-id=1     //服务ID (唯一)

#Slave1

[mysqld]

log-bin=MySQL-bin    //二进制文件

server-id=2     //服务ID

#Slave2

[mysqld]

log-bin=MySQL-bin    //二进制文件

server-id=3     //服务ID

保存退出之后，重启MYSQL服务：

[root@Cent01 ~]# service mysqld restart

Stopping mysqld:                                           [  OK  ]

Starting mysqld:                                           [  OK  ]

3)在Master上创建备份帐号；

[root@Cent01 ~]# mysql -u root -p123.com

mysql> GRANT REPLICATION SLAVE ON *.* to ‘myslave‘@‘10.10.10.7‘ identified by ‘123.com‘;

Query OK, 0 rows affected (0.02 sec)

mysql> GRANT REPLICATION SLAVE ON *.* to ‘myslave‘@‘10.10.10.8‘ identified by ‘123.com‘;

Query OK, 0 rows affected (0.02 sec)

查看Master状态

mysql> show master status;

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

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |

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

| MySQL-bin.000002 |      261 |              |                  |

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

1 row in set (0.00 sec)

4)Slave1 Slave2上设置

[root@Cent01 ~]mysql -u root -p123.com

mysql> change master to

-> MASTER_HOST=‘10.10.10.11‘,

-> MASTER_USER=‘myslave‘,

-> MASTER_PASSWORD=‘123.com‘,

-> MASTER_LOG_FILE=‘MySQL-bin.000001‘,

-> MASTER_LOG_POS=261;

在Slave1,Slave2上启动Slave,并查看状态；

mysql> slave start;

mysql> show slave status \G

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

Slave_IO_State: Waiting for master to send event

Master_Host: 10.10.10.7

Master_User: myslave

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: MySQL-bin.000002

Read_Master_Log_Pos: 261

Relay_Log_File: mysqld-relay-bin.000004

Relay_Log_Pos: 406

Relay_Master_Log_File: MySQL-bin.000002

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

Relay_Log_Space: 707

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)

5)测试Master,Slave同步；

1.在Master上创建数据库信息;

mysql> CREATE DATABASE info;

Query OK, 1 row affected (0.00 sec)

mysql> use info

Database changed

mysql> CREATE TABLE user (id int(3),name varchar(10));

Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO user values (001,‘zhangcheng‘);

Query OK, 1 row affected (0.01 sec)

2.在Slave1,Slave2上查看；

mysql> show databases;

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

| Database           |

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

| information_schema |

| NINESTAR           |

| info               |

| mysql              |

| test               |

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

5 rows in set (0.00 sec)

mysql> use info

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

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

| id   | name       |

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

|    1 | zhangcheng |

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

1 row in set (0.00 sec)

信息已同步！

MySQL主(Master)从(Slave)备份；

标签：mysql   centos

本条技术文章来源于互联网，如果无意侵犯您的权益请点击此处反馈版权投诉

本文系统来源：http://jzhangcheng.blog.51cto.com/9151498/1660019