mysql数据复制、导出、备份和主从同步

MySQL数据管理：复制、导出、备份与主从同步实战

最新推荐文章于 2024-12-30 21:52:11 发布

原创最新推荐文章于 2024-12-30 21:52:11 发布 · 541 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #主从同步 #导出

入门专栏收录该内容

49 篇文章

订阅专栏

本文详细介绍了MySQL数据管理的几个关键操作：如何复制数据结构和数据、导出数据到文件、备份数据文件以及设置主从同步。在复制数据时，可以分别复制结构、数据或两者都复制。使用特定命令行进行数据导出，通过添加-B参数在备份中包含创建数据库的语句。主从同步涉及在master和slave机器上配置唯一的server-id，创建同步账号并授权，最后在slave机器上导入历史数据并开始同步master的数据。

复制数据

复制数据结构及数据

mysql>create table new_table_name as select * from old_table_name;

只复制结构

mysql>create table new_tabe_name as select * from old_table_name where 1=2;

或者

mysql>create table new_table_name like old_table_name;

只复制数据

insert into new_table_name select * from old_table_name;

或者

mysql>insert into new_table_name(column1, column2,...,columnn) select column1,column2,...,columnn from old_table_name;

导出数据到文件

mysql命令行(导出到MySQL sever的路径)：

mysql> select * from table_name into outfile "/path/out_file_name";

bash shell(导出到MySQL客户端机器路径)：

mysql -h10.123.10.10 -uusername -ppwd -Pport -e "select * from table_name" >> /path/out_file_name

备份数据文件

mysqldump -h10.10.10.10 -uusername -ppwd -Pport mydbname > mydbname.sql
mysqldump -h10.10.10.10 -uusername -ppwd -Pport -B mydbname > mydbname.sql
mysqldump -h10.10.10.10 -uusername -ppwd -Pport --all-databases > all_database.sql

其中，-B参数添加后导出的数据包含create database的语句，导入的时候，新数据库不用实现创建表

主从同步

master机器开启log-bin记录，并在mysql配置文件中设置唯一server-id，重启mysql服务
vim my.cnf
```
log-bin=/path/bin-log-name
server-id=1
```
slave机器的mysql配置中设置与master机器不同的唯一server-id，重启mysql服务。
vim my.cnf
```
server-id=2
```

master机器mysql创建同步专用账号，并对slave机器授权

create user 'repl'@'%' identified by "replpwd";
grant replication slave on *.* to "repl"@"%" identified by "replpwd";
flush privileges;

master 机器：

加锁停止更新
```
mysql> set global read_only=1;
```
记下当前更新的日志位置，导出当前数据
```
mysql> show master status\G;
```

导出历史的数据

mysqldump -hmaster-ip -uusername -ppwd -Pport -B mydbname > mydbname.sql

解锁表
```
mysql> set global read_only=0;
```

slave机器：

导入从master导出的历史数据

mysql -hslave-ip -uusername -ppasswd -Pport mydbname < mydbname.sql

开始同步master的数据

mysql> change master to master_host='master_ip',master_user='repl',master_password='replpwd',master_log_gile='file-0001',master_log_pos=12345;
mysql> start slave;

其中，master_log_pos为在master机器记录的log位置（这个点儿之前的数据已经在上一步导入历史数据时导入）