The Ultimate Guide to mysqldump
https://ift.tt/38BF0bN
Introduction
All complex applications require a database of some sort to store information.
MySQL is one of the most popular databases that is used on the web. Most of a businesses
time and value is put into a database and its key to keep that data secure. SnapShooter uses
MySQL to help manage and backup customers servers and databases.
In this guide we will explore the mysqldump utility in depth. We cover the ways
you can make a backup as well as the common errors users experience when using it.
Table of Contents
What is mysqldump?
MySQL is an open-source and one of the most widely used relational database
management system. It is used to store anything from a simple name to a picture gallery or a
large amount of information in a network. mysqldump is a part of the MySQL
relational
database package that is used to dump (backup) a database or a collection of databases into
a simple text file. The text file is a set of SQL statements that are used to reconstruct
the database to its original state.
Generally, mysqldump is used to export the database to a backup file (like we
do at SnapShooter) and also
used to move the database to the other host. The mysqldump command also generates output in
XML, CSV and other delimited text formats.
Basic How to use MySQL Dump
The basic syntax of Mysqldump command is shown below:
mysqldump -u USERNAME -pPASSWORD DBNAME > DBBACKUP.sql
mysqldump -u USERNAME -pPASSWORD --databases DB1 DB2 DB3.. >DBBACKUP.sql
mysqldump -u USERNAME -pPASSWORD --all-databases > ALLDBBACKUP.sqlA brief explanation of each option is shown below:
| -u | It specifies your MySQL username. |
|---|---|
| -p | It specifies your MySQL password. |
| DBNAME | A name of the database that you want to backup. |
| DBBACKUP.sql | The name of the backup file you want to generate. |
| -h | It specifies the hostname of the MySQL database server. |
| –databases | It is used to determine the database. |
| -all-databases | It is used to backup all databases. |
| –default-auth=plugin | It is used to specify the client-side authentication plugin to use. |
| –compress | It is used to enable compression in server/client protocol. |
| -P | It is used to specify the port number to use for MySQL connection. |
How to Backup Single MySQL Database
The simple and easiest way to take a backup of a single database, run the following command:
mysqldump -u root -ppassword wpdb > wpdb_backup.sql
This command will dump the database named wpdb and generate a single file named
wpdb_backup.sql.
How to Backup Multiple MySQL Databases
With mysqldump, you can also take a backup of multiple databases to a single file.
For example, dump the database named wpdb1, wpdb2 and wpdb3 and generate a single backup
file
named wpdb_backup3.sql, run the following command:
mysqldump -u root -ppassword --databases wpdb1 wpdb2 wpdb3 > wpdb_backup3.sqlHow to Backup All MySQL Databases
Mysqldump also allows you to backup all databases in your system.
For example, take a backup of all databases and generate a single backup file
named
alldb_backup.sql, run the following command:
mysqldump -u root -ppassword --all-databases > alldb_backup.sqlHow to Backup Only Structure MySQL Databases
If you want to generate the backup of the database structure, then you must use
the –no-data
option in the mysqldump command.
For example, it generates the backup of the database structure of the wpdb
database run the
following command:
mysqldump -u root -ppassword --no-data wpdb > wpdb_backup.sqlHow to Backup single MySQL table
If you want to generate the backup of the specific table, you will need to
specify the table
name after the database name.
For example, generate the backup of the wptable table of the wpdb database, run
the following
command:
mysqldump -u root -ppassword --no-data wpdb wptable > wpdbtable_backup.sqlHow to Backup MySQL with no structure
If you want to generate the backup of the data without the database structure,
then you must use
the –no-create-info option in the mysqldump command.
For example, generates the backup of data of the wpdb database, run the
following command:
mysqldump -u root -ppassword wpdb --no-create-info > wpdb_backup.sqlBasic How to Import a mysqldump
The simple and easiest way to import the database using the following command:
mysql -u root -ppassword wpdb < wpdb_backup.sql Note: You will need to create an empty database on the target machine before
importing database.
You can also import the database with mysqlimport command if you want to
restore a database that already exists on the targeted machine.
mysqlimport -u root -ppassword wpdb < wpdb_backup.sqlIf you want to import all databases, run the following command:
mysql -u root -ppassword < alldb_backup.sqlMySQLDump what does the –quick flag do?
mysqldump works by retrieving and dumping table contents row by row or
retrieving the entire
content from a table and buffer it in memory before dumping it. This will cause a problem if
you
are exporting large tables.
Use mysqldump command with –quick flag reads out large tables in a way that doesn’t require
large amounts RAM to fit the full table in memory. This will provides extra safety for
systems
with little RAM and large databases.
Can you use Where clause with MySQLDump?
Of course, it’s easy to use a where clause with mysqldump command. If you have a
database with lots of tables and each table has a billion records, and if you want the
records for a specific year, then you can use where clause with mysqldump command. Where
clause allows you to pass a string for the condition and fetch the specific records as you
need.
For example, fetch all records from the table named mytable and database named
wpdb where the field date_created is greater than ‘2018-02-01’, run the following command:
mysqldump -u root -ppassword wpdb --tables mytable --where="date_created > '2018-02-01' " > wpdb_myrecord.sqlmysqldump Common Errors and Fixes
mysqldump: error 2013: lost connection to mysql
server during query when dumping table?
You should increase the max_allowed_packet, net_write_timeout, net_read_timeout
and innodb_buffer_pool_size value to appropriate levels to fix the error.
To set the above value, edit the MySQL configuration file :
nano /etc/mysql/my.cnf
Add the following lines under the [mysqld] and [mysqldump] section:
[mysqld]
innodb_buffer_pool_size=100M
max_allowed_packet=1024M
[mysqldump]
max_allowed_packet=1024M
net_read_timeout=3600
net_write_timeout=3600
Save and close the file when you are finished. Then, restart the MySQL service to effect the
changes.
service mysql restart
Or
systemctl restart mysqld
mysqldump: error 2020: got packet bigger than ‘max_allowed_packet’ bytes when dumping table?
When the database you are trying to backup is very large, and the
max_allowed_packet value was set to a low value. This will cause the above error.
You can fix the error by editing the MySQL configuration file:
nano /etc/mysql/my.cnf
Increase the max_allowed_packet value the under the [mysqld] and [mysqldump]
section
[mysqld]
max_allowed_packet=your-desired-value
[mysqldump]
max_allowed_packet=your-desired-value
Save and close the file when you are finished. Then, restart the MySQL service to effect the
changes.
service mysql restart
Or
systemctl restart mysqld
mysqldump when using lock tables?
By default, mysqldump command will perform a lock on all tables until the
backup is complete. But this is a poor option in a live environment where the database is
extensive, and uptime is necessary.
Primarily it’s used for protection of data integrity when dumping MyISAM tables. MyISAM
tables
require this locking because they don’t support transactions.
If you use a mix of MyISAM and InnoDB tables, then dump your MyISAM tables separately from
InnoDB tables using --lock-tables option.
InnoDB is the default table storage engine nowadays. So you can skip the lock
tables option by
using --skip-lock-tables to stop the behaviour and
--single-transaction to run mysqldump within
a transaction.
Couldn’t execute ‘show create table x’ Table
does not exist (1146)
Some times the table was deleted during the backup process. In that case, you can restrict
certain tables from the mysqldump command using the –ignore-table option.
You will need to specify both database and table names as shown below:
mysqldump -u root -ppassword dbname --ignore-table=tablename > db_backup.sqlYou can also ignore the multiple tables by specifying the option per table as
shown below:
mysqldump -u root -ppassword dbname --ignore-table=table1 --ignore-table=table2 > db_backup.sqlmysqldump unknown database when selecting the
database
Usually, this error occurs when you specify the password on the command line with -p flag
and there is a space between -p and password.
For example, if you want to dump the database named wpdb using the user “root”
and password “your-password”, and use the following command:
The above command will produce an error says ""Unknown database your-password" when selecting the database".Actually, you must specify the password with no space after -p switch as shown
below:
mysqldump -u root -pyour-password wpdb > wpdb_backup.sqlmysqldump got error 1044 when selecting the
database
You will get this error if you are trying to dump the database with the user which has not
enough privileges to access the selected database.
To fix this error, assign proper privileges to the user to access the database.
First, login to MySQL with the following command:
mysql -u root -pProvide your MySQL root password then grant all privileges on the database to
your user:
GRANT ALL PRIVILEGES ON wpdb.* TO 'your_user'@'localhost';Next, flush the privileges and exit from the MySQL with the following
command:
FLUSH PRIVILEGES;
EXIT;mysqldump access denied for user when trying to
connect
There are several reasons for this error. Most common reasons for this error are
listed below:
1. Wrong mysqldump Command
One of the common reasons for this error is that when you use the wrong format
of mysqldump command.
For example, the general syntax for taking database backup using mysqldump is shown below:
mysqldump -u user -ppasword database > database.sql
You will get the above error if you take a database backup without specifying a username and
password as shown below:
mysqldump database > database.sqlYou should get the following error:
mysqldump: Got error: 1045: "Access denied for user 'user' @ 'localhost' (using password: NO)" when trying to connect2. Wrong user credentials
You will also get the above error if you use the wrong username and password
while connecting to
the database. Therefore, MySQL can’t verify the authenticity of the account and throws this
error.
3. Remote host does not allowed to connect to database
You will also see this error if you are trying to backup the database on the
remote server.
Because, remote host disallow external connections and they only allow to connect from the
localhost.
To fix this, configure your MySQL server to allow connections from the remote
host.
How to mysqldump backup large database
Some useful tips and tricks while using the mysqldump for a very large database.
1. How to Compress mysqldump Output
It is a good idea to compress the database backup in gzip format to reduce the
size of the
database.
Run the following command to dump the database named wpdb and gzip it at the
same time:
mysqldump -u root -ppassword wpdb | gzip > wpdb_backup.sql.gzIf you want to restore the compressed database, run the following command:
gunzip < wpdb_backup.sql.gz | mysql -u root -ppassword wpdb2. How to import the large MySQL database
First, login to the MySQL shell using the following command:
mysql -u root -pProvide your MySQL root password then set network buffer length to a large byte
number as shown
below:
set global net_buffer_length=1000000;Next also set maximum allowed packet size to a large byte number as shown
below:
set global max_allowed_packet=1000000000;Next, disable foreign key checking to avoid delays, errors and unwanted
behaviour as shown
below:
SET foreign_key_checks = 0;
SET UNIQUE_CHECKS = 0;
SET AUTOCOMMIT = 0;Next, import your dump file with the following command:
source /backup-path/wpdb.sqlOnce you are done, enable foreign key checks with the following command:
SET foreign_key_checks = 1;
SET UNIQUE_CHECKS = 1;
SET AUTOCOMMIT = 1;3. Separate databases into separate data files
If you have a large database, then you can also split them by separate into
separate data files.
You can create a new file with a list of all databases using the following
command:
mysql -u root -ppassword -A --skip-column-names -e"SELECT schema_name FROM
information_schema.schemata WHERE schema_name NOT IN ('information_schema','mysql')" >
db_list.txtOnce you have the list of databases, you can run a loop with mysqldump command
through the list
as shown below:
for DB in `cat db_list.txt`
do
mysqldump -u root -ppassword --hex-blob --routines --triggers ${DB} | gzip > ${DB}.sql.gz &
done
waitMysqldump without password
When you dump a database with mysqldump, you will need to specify a username and
password with the command. If you don’t want to specify a username and password with
mysqldump command every time, you just need to create a file in your home directory with
MySQL credential. This will disable the mysqldump password prompting.
To do so, create a new file in your home directory:
nano ~/mysql.txtAdd your MySQL root credential, as shown below:
[mysqldump]
user=root
password=passwordSave and close the file when you are finished.
Now, you can specify your MySQL credential file using the option –defaults-file
as shown below:
mysqldump --defaults-file=~/mysql.txt wpdb > wpdb_backup.sqlprogramming
via Laravel News Links https://ift.tt/2dvygAJ
July 5, 2020 at 10:24PM
