Setting Character Set in mysqldump Output

The mysqldump command in MySQL is used to create a backup of MySQL databases. One useful option is --set-charset, which allows you to specify the character set for the output of the dump file. This option ensures that the data in the dumped file is encoded in the specified character set, which can be crucial for maintaining data integrity and compatibility across different systems.

Here are several examples demonstrating the usage of --set-charset in mysqldump:

Example 1: Dumping a database with UTF-8 character set:

    mysqldump --set-charset=utf8 mydatabase > mydatabase_utf8.sql
    

This command dumps the mydatabase database into a SQL file named mydatabase_utf8.sql with UTF-8 character encoding.

Example 2: Dumping a specific table with Latin1 character set:

    mysqldump --set-charset=latin1 mydatabase mytable > mytable_latin1.sql
    

Here, the command dumps only the mytable table from the mydatabase database into mytable_latin1.sql using Latin1 character encoding.

Example 3: Dumping multiple databases with UTF-8 encoding:

    mysqldump --set-charset=utf8 --databases db1 db2 db3 > multi_db_utf8.sql
    

This command dumps db1, db2, and db3 databases into a single file multi_db_utf8.sql using UTF-8 character encoding.

Example 4: Dumping all databases with a specific character set:

    mysqldump --set-charset=latin1 --all-databases > all_databases_latin1.sql
    

Here, all databases on the MySQL server are dumped into all_databases_latin1.sql with Latin1 character encoding.

Example 5: Dumping a database with default character set:

    mysqldump mydatabase > mydatabase_default.sql
    

In this case, the command dumps mydatabase using the default character set of the MySQL server.

Verification: To verify if the mysqldump command executed successfully, you can check the size and content of the generated SQL files using a text editor or by importing them back into another MySQL database instance. For instance:

    mysql -u username -p mydatabase < mydatabase_utf8.sql
    

This command imports the SQL file back into the mydatabase database. If no errors occur during import, it confirms that the dump was successful.

Also check similar articles.

Dumping Routines (Functions and Procedures) with mysqldump
Directing Output to File in mysqldump
Using REPLACE INTO Instead of INSERT INTO in mysqldump
Quoting Table and Column Names in mysqldump Output
Dumping Quickly without Buffering in mysqldump