MySQL | mysqldump 导入数据库失败

Blog迁出腾讯云,数据迁移

在新服务器命令行下执行

mysqldump -h旧服务器地址 -P端口 -uroot -p blog > blog.sql

导出数据库为sql文件(数据量较小)

然后在新服务器上创建数据库后执行

mysqldump -uroot -p blog < blog.sql

执行完后显示mysqldump complete
但是进到数据库里发现并没有导入任何数据

于是在mysql命令行里执行

source /root/blog.sql

显示多条执行完成的反馈

use blog
show tables;

所有表都有了,打开blog访问正常

扩展阅读

MYSQLDUMP TO RESTORE AND BACKUP MYSQL DATABASE

使用mysqldump还原和备份MySQL数据库

When you need to handle MySQL databases, it is useful to have a tool which lets you create backup files and also restore them properly; this can be achieved by using mysql and mysqldump commands in the command line.

To be able to use them, you have to make sure the user you will use has the privileges to manage the database you are working with, we will be using root MySQL user to go through the examples.

Are you in a rush? Read this summary (don’t forget to type password after):

    backup ~: mysqldump -u root -p [database_name] > backupfilename.sql
    Enter password:

    restore ~: mysql -u root -p [database_name] < backupfilename.sql
    Enter password:

How to backup a single MySQL database 单库备份

One of the possible solutions can be mysqldump which is an effective tool to backup MySQL databases. It creates a *.sql file with DROP table, CREATE table and INSERT into sql-statements of the source database.

The next example takes a backup of wordpressdb database and dumps the output to wordpressdb.sql

    ~: mysqldump -u root -p wordpressdb > wordpressdb.sql
    Enter password:

The wordpressdb.sql file will contain commands to drop tables, create tables and insert for all the tables in the wordpressdb database, that is your backup file.

How to backup multiple MySQL databases 多库备份

Let’s start identifying what databases are we going to back up. Login to MySQL using the user and password you have (we will use root user) after this, you can list current databases managed by the user typing SHOW DATABASES;:

    ~: mysql -u databaseman -p
    Enter password:

    mysql> SHOW DATABASES;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | wordpressdb        |
    | sugarcrmdb         |
    | testdb             |
    +--------------------+
    4 rows in set (0.00 sec)

Now, assuming you want to take backup of both wordpressdb and sugarcrmdb database, use the mysqldump as we do it below:

    ~: mysqldump -u root -p --databases wordpressdb sugarcrmdb > wpandsugardbbk.sql
    Enter password:

After this,you can also verify the new file contains the info you want executing:

    ~: grep -i "Current database:" /tmp/wpandsugardbbk.sql
    -- Current Database: `wordpressdb`
    -- Current Database: `sugarcrmdb`

How to backup all MySQL database 全库备份

Same principles, you just have to add the --all-databases to mysqldump command as shown below:

    ~: mysqldump -u root -p --all-databases > /your-path/all-database.sql
    Enter password:

How to restore MySQL database 还原

Did you realize about the > operator used in the command line when backing up data? Well, restoring will use the sign with the opposite direction <. We will use mysql command to restore the wordpressdb database, shown below. When you are restoring the wordpressdb.sql on a remote database, make sure to create the wordpressdb database before you can perform the restore (we do this with our root user ).

    ~: mysql -u root -p
    Enter password:

    mysql> CREATE DATABASE wordpressdb;
    Query OK, 1 row affected (0.02 sec)

    mysql> exit;

    ~: mysql -u root -p wordpressdb < /your-path/wordpressdb.sql
    Enter password:

If you want to make sure your database has the imported data, you have to login MySQL again, select the wordpressdb database with the command USE and then list the tables of it using SHOW TABLES; like this:

    ~: mysql -u root -p
    Enter password:

    mysql> USE wordpressdb;
    Database changed

    mysql> SHOW TABLES;
    +-------------------------------------------------+
    | Tables_in_wordpressdb                           |
    +-------------------------------------------------+
    | wp_commentmeta                                  |
    | wp_comments                                     |
    | wp_links                                        |
    | wp_options                                      |
    | wp_postmeta                                     |
    | wp_posts                                        |
    | wp_term_relationships                           |
    | wp_term_taxonomy                                |
    | wp_termmeta                                     |
    | wp_terms                                        |
    | wp_usermeta                                     |
    | wp_users                                        |
    | wp_wc_download_log                              |
    | wp_wc_webhooks                                  |
    +-------------------------------------------------+
    14 rows in set (0.00 sec)

    mysql> exit;

source page:
MYSQLDUMP TO RESTORE AND BACKUP MYSQL DATABASE

本文链接:

https://blog.zhigu34.cn/archives/71.html
1 + 3 =
快来做第一个评论的人吧~