最新消息:20210917 已从crifan.com换到crifan.org

[已解决]sql拷贝数据库

MySQL crifan 2178浏览 0评论

sql copy database

mariadb copy database

Backup and Restore Overview – MariaDB Knowledge Base

mysql – How to copy a database using HeidiSQL? – Stack Overflow

Clone MySQL database – Stack Overflow

How To Backup and Restore MySQL and MariaDB Databases the Easy Way! | NorthTech Computer

Best practices for maria db and mysql backups

sql copy database in commandline

MySQL copy/duplicate database – Stack Overflow

Best practices for maria db and mysql backups

mysql copy database command

Duplicate Entire MySQL Database – Stack Overflow

最后用的是:

Last login: Thu Jan 21 10:42:42 2016 from 221.224.209.89
[useradmin@AZWECHAT02 ~]$ ls
[useradmin@AZWECHAT02 ~]$ pwd
/home/useradmin
[useradmin@AZWECHAT02 ~]$ uname -a
Linux AZWECHAT02 3.10.0-229.1.2.el7.x86_64 #1 SMP Fri Mar 27 03:04:26 UTC 2015 x86_64 x86_64 x86_64 GNU/Linux
[useradmin@AZWECHAT02 ~]$ 
[useradmin@AZWECHAT02 ~]$ 
[useradmin@AZWECHAT02 ~]$ mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 27961440
Server version: 5.5.44-MariaDB MariaDB Server
Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
MariaDB [(none)]> show databases;
+——————–+
| Database           |
+——————–+
| information_schema |
| dev_qoros          |
| guanzhi            |
| mysql              |
| performance_schema |
| qwechat            |
+——————–+
6 rows in set (0.00 sec)
MariaDB [(none)]> use guanzhi
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 [guanzhi]> select * from staffs where staff_no=’P1503′;
+—-+————+————-+——————–+———-+————+
| id | staff_name | staff_phone | staff_creditcard   | staff_no | has_select |
+—-+————+————-+——————–+———-+————+
| 86 | 寮      | 15335292985 | 341225198704114617 | P1503    | 2          |
+—-+————+————-+——————–+———-+————+
1 row in set (0.00 sec)
MariaDB [guanzhi]> CREATE DATABASE guanzhi_removeDuplicate
    -> quit
    -> Ctrl-C — exit!
Aborted
[useradmin@AZWECHAT02 ~]$ mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 27987822
Server version: 5.5.44-MariaDB MariaDB Server
Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
MariaDB [(none)]> use guanzhi
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 [guanzhi]> CREATE DATABASE guanzhi_removeDuplicate;
Query OK, 1 row affected (0.06 sec)
MariaDB [guanzhi]> show databases;
+————————-+
| Database                |
+————————-+
| information_schema      |
| dev_qoros               |
| guanzhi                 |
| guanzhi_removeDuplicate |
| mysql                   |
| performance_schema      |
| qwechat                 |
+————————-+
7 rows in set (0.00 sec)
MariaDB [guanzhi]> mysqldump -u root -p guanzhi | mysql -u root -p guanzhi_removeDuplicate
    -> Ctrl-C — exit!
Aborted
[useradmin@AZWECHAT02 ~]$ mysqldump -u root -p guanzhi | mysql -u root -p guanzhi_removeDuplicate
Enter password: Enter password: 
we^H^H^H^H^H^CCtrl-C — exit!
[useradmin@AZWECHAT02 ~]$ mysqldump -u root -p guanzhi | mysql  guanzhi_removeDuplicate
Enter password: ERROR 1045 (28000): Access denied for user ‘useradmin’@’localhost’ (using password: NO)
mysqldump: Got error: 1045: "Access denied for user ‘root’@’localhost’ (using password: NO)" when trying to connect
[useradmin@AZWECHAT02 ~]$ mysqldump -u root -p guanzhi | mysql -u root -p guanzhi_removeDuplicate
Enter password: Enter password: 
^CCtrl-C — exit!
[useradmin@AZWECHAT02 ~]$ mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 27997643
Server version: 5.5.44-MariaDB MariaDB Server
Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
MariaDB [(none)]> use guanzhi;
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 [guanzhi]> mysqldump -u root -p guanzhi | mysql -u root -p guanzhi_removeDuplicate;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘mysqldump -u root -p guanzhi | mysql -u root -p guanzhi_removeDuplicate’ at line 1
MariaDB [guanzhi]> mysqldump -u root -p guanzhi | mysql -u root -p xxx
guanzhi_removeDuplicate;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘mysqldump -u root -p guanzhi | mysql -u root -p xxx guanzhi_removeDuplic’ at line 1
MariaDB [guanzhi]> mysqldump -u root -p guanzhi | mysql -u root -pxxx
 guanzhi_removeDuplicate;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘mysqldump -u root -p guanzhi | mysql -u root -pxxx 4 guanzhi_removeDuplica’ at line 1
MariaDB [guanzhi]> mysqldump -u root -pxxx guanzhi | mysql -u root -pxxx guanzhi_removeDuplicate;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘mysqldump -u root -pxxx
 guanzhi | mysql -u root -pxxx guanzhi_re’ at line 1
MariaDB [guanzhi]> quit
Bye
[useradmin@AZWECHAT02 ~]$ mysqldump -u root -pxxx guanzhi | mysql -u root -pxxx guanzhi_removeDuplicate
[useradmin@AZWECHAT02 ~]$ mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 28009641
Server version: 5.5.44-MariaDB MariaDB Server
Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
MariaDB [(none)]> show databases;
+————————-+
| Database                |
+————————-+
| information_schema      |
| dev_qoros               |
| guanzhi                 |
| guanzhi_removeDuplicate |
| mysql                   |
| performance_schema      |
| qwechat                 |
+————————-+
7 rows in set (0.01 sec)
MariaDB [(none)]> use guanzhi_removeDuplicate
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 [guanzhi_removeDuplicate]> show tables;
+———————————–+
| Tables_in_guanzhi_removeDuplicate |
+———————————–+
| cars                              |
| results                           |
| selection                         |
| staffs                            |
+———————————–+
4 rows in set (0.00 sec)
MariaDB [guanzhi_removeDuplicate]>

精简的来说是:

1.进入mysql,创建用于备份的新的数据库:

[useradmin@AZWECHAT02 ~]$ mysql -u root -p
MariaDB [guanzhi]> CREATE DATABASE guanzhi_removeDuplicate;

2.回到Linux命令行中,用msqldump导出并创建新的数据库:

[useradmin@AZWECHAT02 ~]$ mysqldump -u root -pYourPassword guanzhi | mysql -u root -pYourPassword guanzhi_removeDuplicate

3.可以再去进入数据库去验证,看看是否已经拷贝了新的数据库,新的数据库中是否有了新的数据:

[useradmin@AZWECHAT02 ~]$ mysql -u root -p
Enter password: 
MariaDB [(none)]> show databases;
+————————-+
| Database                |
+————————-+
| information_schema      |
| dev_qoros               |
| guanzhi                 |
| guanzhi_removeDuplicate |
| mysql                   |
| performance_schema      |
| qwechat                 |
+————————-+
7 rows in set (0.01 sec)
MariaDB [(none)]> use guanzhi_removeDuplicate
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 [guanzhi_removeDuplicate]> show tables;
+———————————–+
| Tables_in_guanzhi_removeDuplicate |
+———————————–+
| cars                              |
| results                           |
| selection                         |
| staffs                            |
+———————————–+
4 rows in set (0.00 sec)
MariaDB [guanzhi_removeDuplicate]>

可见是可以了。

转载请注明:在路上 » [已解决]sql拷贝数据库

发表我的评论
取消评论

表情

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址
88 queries in 0.241 seconds, using 20.12MB memory