mysql 热备份

以下为在windows 环境下己测试成功，至于liunx应该差不多

A：主服务器

IP：192.168.9.203

B：从服务器

IP：192.168.9.131

 

1、在A的数据库中建立一个备份帐户，命令如下：
GRANT REPLICATION SLAVE,RELOAD,SUPER ON *.*
TO backup@'192.168.9.131'
IDENTIFIED BY ‘1234’;

说明：backup(用户名) 192.168.9.131(Ip地址) 1234(密码）

 

2、因为mysql版本新密码算法不同，所以进入mysql下，输入：set password for 'backup'@'192.168.100.2'=old_password('1234');

 

3、关停A服务器，将A中的数据拷贝到B服务器中，使得A和B中的数据同步，并且确保在全部设置操作结束前，禁止在A和B服务器中进行写操作，使得两数据库中的数据一定要相同！

 

4、对A服务器的配置进行修改，打开mysql/my.ini文件，在[mysqld]下面添加如下内容：
server-id=1
log-bin=c:/log-bin.log

server-id：为主服务器A的ID值
log-bin：二进制变更日值

 

5、重启A服务器，从现在起，它将把客户端有关数据库的修改记载到二进制变更日志里去

6、关停B服务器，对B服务器配置，以便让它知道自己的镜像ID、到哪里去找主服务器以及如何去连接服务器。最简单的情况是主、从服务器分别运行在不同的主机上并都使用着默认的TCP/IP端口，只要在从服务器启动时去读取的mysql/my.ini文件里添加以下指令就行了。

[mysqld]

server-id=2

7、启动B数据库执行以下命令

Change master to

master-host=’192.168.9.203’

master-user=’backup’,

master-password=’1234’.

然后再执行

Start slave

 

 

=============================================

1.GRANT REPLICATION SLAVE,SUPER,RELOAD  ON *.* TO 'repl'@'%' IDENTIFIED BY 'slavepass';
2. FLUSH TABLES WITH READ LOCK;
3. UNLOCK TABLES;
4. START SLAVE;
CHANGE MASTER TO MASTER_HOST='192.168.0.101',MASTER_PORT=3307,MASTER_USER='repl',MASTER_PASSWORD='slavepass',MASTER_LOG_FILE='mysql-bin.000011',MASTER_LOG_POS=997;

LOAD TABLE FROM MASTER or LOAD DATA FROM MASTER
LOAD DATA FROM MASTER;
SHOW PROCESSLIST/G;
show SLAVE STATUS/G;
show master status/G;

LOAD DATA FROM MASTER currently works only if all the tables on the master use the MyISAM storage engine
The retry interval is controlled by the --master-connect-retry option. The default is 60 seconds.

mysql双备份

1.Make sure that the versions of MySQL installed on the master and slave are compatible according to the table
查看服务器版本　mysql -V
2.Set up an account on the master server that the slave server can use to connect.

GRANT REPLICATION SLAVE,SUPER,RELOAD ON *.* TO 'repl'@'%.mydomain.com' IDENTIFIED BY 'slavepass';

3.
 Flush all the tables and block write statements by executing a FLUSH TABLES WITH READ LOCK statement:
 mysql> FLUSH TABLES WITH READ LOCK;
 To use tar to create an archive that includes all databases, change location into the master server's data directory, then execute this command:
 shell> tar -cvf /tmp/mysql-snapshot.tar .
 If you want the archive to include only a database called this_db, use this command instead:
 shell> tar -cvf /tmp/mysql-snapshot.tar ./this_db
 Then copy the archive file to the /tmp directory on the slave server host.
 On that machine, change location into the slave's data directory, and unpack the archive file using this command:
 shell> tar -xvf /tmp/mysql-snapshot.tar

 While the read lock placed by FLUSH TABLES WITH READ LOCK is in effect, read the value of the current binary log name and offset on the master:
 mysql > SHOW MASTER STATUS;
 The File column shows the name of the log, while Position shows the offset.

 After you have taken the snapshot and recorded the log name and offset, you can re-enable write activity on the master:

 mysql> UNLOCK TABLES;
 
mysql> FLUSH TABLES WITH READ LOCK;
mysql> SHOW MASTER STATUS;
shell> mysqladmin -u root shutdown

4.Make sure that the [mysqld] section of the my.cnf file on the master host includes a log-bin option.
[mysqld]
log-bin=mysql-bin
server-id=1

5.Stop the server that is to be used as a slave server and add the following to its my.cnf file:
[mysqld]
server-id=2
6.If you made a binary backup of the master server's data, copy it to the slave server's data directory before starting the slave.
7.Start the slave server.
8.If you made a backup of the master server's data using mysqldump, load the dump file into the slave server:

shell> mysql -u root -p < dump_file.sql
9.Execute the following statement on the slave, replacing the option values with the actual values relevant to your system:

mysql> CHANGE MASTER TO
    ->     MASTER_HOST='master_host_name',
    ->     MASTER_USER='replication_user_name',
    ->     MASTER_PASSWORD='replication_password',
    ->     MASTER_LOG_FILE='recorded_log_file_name',
    ->     MASTER_LOG_POS=recorded_log_position;

10.Start the slave threads:

mysql> START SLAVE;

implement:

 modify
 my-large.ini
 port  = 3307
 server-id = 1

 my-large.ini
 port  = 3308
 server-id = 2
install mysql(notice change to install dir/bin)
 mysqld-nt install mysqltest1  --defaults-file=F:/server/mysqltest1/my-large.ini
 mysqld-nt install mysqltest2  --defaults-file=F:/server/mysqltest2/my-large.ini
 net start mysqltest1
 net start mysqltest2
configuration 
 on master host
  GRANT REPLICATION SLAVE,SUPER,RELOAD  ON *.* TO 'repl'@'%' IDENTIFIED BY '123';
  FLUSH TABLES WITH READ LOCK;
  show proccesslist /G;
  show master status /G;//recorded the log name and offset
  UNLOCK TABLES;
 on slave host
  mysql -P3308 -u root
  CHANGE MASTER TO MASTER_HOST='192.168.0.101',MASTER_PORT=3307,MASTER_USER='repl',MASTER_PASSWORD='slavepass',MASTER_LOG_FILE='mysql-bin.000011',MASTER_LOG_POS=997;
  slave start
  show proccesslist /G;
  show slave status /G;