mysql数据迁移以及加锁

一、跨服务器mysql数据迁移

1、在源服务器的源数据库中导出数据文件

使用x shell登录到源服务器并进入mysql命令行模式下

#指定要操作的数据库
use db_name
​
#my_table_name为指定数据表名
#fans.txt为生成的输出文件的名称 这里有个坑 保存路径的位置必须是具有mysql权限的位置 详细见下面的“注意项” 经测试 公司的服务器在保存时必须写如下的完整路径
select * from my_table_name into outfile '/var/lib/mysql-files/fans.txt';
​

注意项

在mysql执行load data infile和into outfile命令都需要在mysql开启了secure_file_priv选项，可以通过show global variables like '%secure%';查看mysql是否开启了此选项，默认值Null标识不允许执行导入导出命令。通过vim /etc/my.cnf修改mysql配置项，将secure_file_priv的值设置为空：

2、访问源服务器复制文件

在目标服务器访问源服务器，复制文件到本地。

#拷贝单个文件命令
#username为用户名  ip为源服务器的ip file_path为文件存放路径 local_path为本地保存路径
#注意冒号后面没有空格（加了会报错）
scp file username@ip:file_path local_path
​
例如: scp file root@172.16.95.119:/var/lib/mysql-files/fans.txt .
注意最后面有个.

​

3、将文件移动到mysql权限的目录下

mv命令
例如： mv fans.txt /var/lib/mysql-files/

4、在目标数据库导入文件

使用x shell登录到目标服务器并进入mysql命令行模式下

假使上述步骤已完成，即fans.txt文件已存在于/var/lib/mysql-files/ 目录下

#low-priority MySQL会等到没有其他人读这个表的时候，才把插入数据
#ignore 跳过有唯一键的现有行的重复行的输入
#file_path 文件路径 "/var/lib/mysql-files/fans.txt"
#my_tabel_name 导入的数据表名
#如果提示错误可以将low-priority删除再试一遍
load data low-priority infile "file_path" ignore into table my_tabel_name;
#当数据量大时，此过程较慢 可能会花20秒以上

备注

replace和ignore关键词的使用

控制对现有的唯一键记录的重复的处理。如果你指定replace，新行将代替有相同的唯一键值的现有行。如果你指定ignore，跳过有唯一键的现有行的重复行的输入。如果你不指定任何一个选项，当找到重复键时，出现一个错误，并且文本文件的余下部分被忽略。

 

二、mysql加锁

1、全局锁

加全局读锁的方法，命令是

Flush tables with read lock；

当你需要让整个库处于只读状态的时候，可以使用这个命令，之后其他线程的以下语句会被阻塞：数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句。

退出客户端后自动释放锁

2、表级锁

表锁的语法是

 #my_table_name为要操作的表名
 #read和write选择一个
 lock tables my_table_name read/write
 #read权限表示只读 阻塞写线程
 #write 阻塞读写线程
 #例如
 lock tables t1 read, t2 write;
 #解锁
 unlock tables;

注意

如果在某个线程 A 中执行 lock tables t1 read, t2 write;这个语句，则其他线程写 t1、读写 t2 的语句都会被阻塞。同时，线程 A 在执行 unlock tables 之前，也只能执行读t1、读写 t2 的操作。连写 t1 都不允许，自然也不能访问其他表。

释放锁

可以用 unlock tables 主动释放锁，也可以在客户端断开的时候自动释放