mysql 热备份
来源:互联网 发布:阿sa和郑中基 知乎 编辑:程序博客网 时间:2024/04/19 18:33
以下为在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;
- MySQL实施热备份
- mysql双机热备份
- mysql 热备份
- mysql 热备份问题
- Mysql 数据库热备份
- Mysql热备份总结
- Mysql热备份总结
- mysql主从热备份
- MySQL数据库热备份
- Mysql 数据库热备份
- mysql 热备份
- mysql 热备份2
- mysql主从热备份
- mysql数据库热备份
- Mysql的双机热备份
- MySQL双机热备份实施方案
- 配置 MySQL 双机热备份
- mysql热备份和恢复
- 用 Java 写GUI 程序
- asp.net利用RAR实现文件压缩解压缩
- LCDS2.6不再集成webtier方式的集成(动态访问mxml和jsp嵌入mxml的tag)。需要通过集成webtier.war来处理
- qtopia-free-src-2.2.0交叉编译:
- 比较好的html在线编辑器介绍
- mysql 热备份
- FCKeditor2.6.3基本设置、使用说明
- 使用批处理命令+windows计划任务实现oracle数据库自动备份
- 计算机基础知识
- 侯捷对进入IT行业的年轻人的建议
- mysql 5.0存储过程学习总结
- [bug解决]程序无法安装
- asp.net2.0下利用javascript实现treeview中的checkbox全选
- Oracle里抽取随机数的多种方法详解