mysql数据迁移:冷备份
环境
- 两台centos7.5
- 关闭防火墙和selinux
- A 192.168.153.179
B 192.168.153.178
部署:
A端:
1、安装mysql数据库
[root@localhost ~]# yum -y install mariadb mariadb-server
2、启动数据库
[root@localhost ~]# systemctl start mariadb
3、进入mysql操作
[root@localhost ~]# mysql
4、创建数据库,创建表,添加数据
创建dyw数据库,字符集为utf8
MariaDB [(none)]> create database dyw charset utf8;
Query OK, 1 row affected (0.00 sec)
创建yy表
MariaDB [(none)]> use dyw;
Database changed
MariaDB [dyw]> create table yy(id int(3),name varchar(12));
Query OK, 0 rows affected (0.00 sec)
插入1条数据
MariaDB [dyw]> insert yy values(0,'张三');
Query OK, 1 row affected (0.00 sec)
MariaDB [dyw]> select * from yy;
+------+--------+
| id | name |
+------+--------+
| 0 | 张三 |
+------+--------+
1 row in set (0.00 sec)
5、关闭mysql
[root@localhost ~]# mysqldump shutdown
6、打包/var/lib/mysql/数据
[root@localhost lib]# tar cf mysql.tar mysql/
7、将mysql.tar传送到B服务器
[root@localhost lib]# scp mysql.tar 192.168.153.178:/root
B端:
1、安装mysql服务
[root@localhost ~]# yum -y install mariadb mariadb-server
2、启动mysql
[root@localhost ~]# systemctl start mariadb
3、关闭mysql
[root@localhost ~]# mysqladmin shutdown
4、删除mysql原来datadir目录
[root@localhost ~]# rm -rf /var/lib/mysql/
5、更改mysql主配置文件
[root@localhost ~]# vim /etc/my.cnf
6、解压mysql.tar
[root@localhost ~]# tar xf mysql.tar
7、创建新定义mysql目录
[root@localhost ~]# mkdir /data_mysql
8、复制mysql目录内数据到创建目录
[root@localhost ~]# cp -r mysql /data_mysql/
9、修改mysql数据目录属主和属组
[root@localhost ~]# chown -R mysql:mysql /data_mysql/
10、给与test目录权限
[root@localhost ~]# chmod 700 /data_mysql/mysql/test/
11、启动mysql服务
[root@localhost ~]# systemctl start mariadb
12、进入mysql查看结果是否成功
[root@localhost ~]# mysql
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| dyw |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)
MariaDB [(none)]> use dyw;
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
MariaDB [dyw]> select * from yy;
+------+--------+
| id | name |
+------+--------+
| 0 | 张三 |
+------+--------+
1 row in set (0.00 sec)