MySQL Restoring And Dumping/Backing UP MySQL Data/Tables/Databases

Backing MySQL Databases

Backing Up/Dumping All Mysql Databases To A Single File

mysqldump --all-databases -u admin -p > allmysqldatabases.sql

The "-all-databases" clause is pretty obvious isn't it?  It means that it will backup all databases.

The "-u admin" means login using the user "admin", if you have another user such as root or a specific user that can access/dump all databases you can use that one.

The "> allmysqldatabases.sql" part tells it to output all the database information to a file called "allmysqldatabases.sql" and don't forget the ">" as that is redirection and is what writes all the info to a file.  Without redirecting the output, you'll see lines and lines of gibberish on your screen, because otherwise MySQL assumes you want to print all the output to the screen.

This method is a quick and dirty way to migrate several databases at once.  After that if you want to get fancy, you can gzip your database dump, this will protect against unknown data corruption and of course make the file transfer quicker since compressing with gzip reduces the size.

For example the file was originally 279MB and gzip took it to 179MB.

To gzip your backed up mysql databases just run this command (works on any file other than just .sql dumps of course):

gzip allmysqldatabases.sql and it leaves you with "allmysqldatabases.sql.gz" in compressed gzip format.

Backing Up A Single Mysql Database

mysqldump --databases mysql -u admin -p > mysqlbackup.sql

The "--databases" clause specifies you are going to backup one or more database as specified in the command.

If you wanted databases "mysql" and "someothedb" you would use "--databases mysql someotherdb"

Backing Up A Specific Table

mysqldump -u user -p databasename tablename tablename > thetable.sql

As you can see after specifying the db name you can specific as many tables you want.  By default the tables will be populated with the data.  If you just want the structure use this instead.

mysqldump -d -u user -p databasename tablename tablename > thetable.sql

The "-d" switch causes only the structure to be dumped.

Restoring MySQL Databases

What fun is backing up without being able to restore?  Restoring can be a bit more tricky and there's a few different scenarios and options you have. 

I'll focus on the most common one, a new MySQL Server install with no previous data or tables.

I'm assuming you've transferred your database backup "allmysqldatabases.sql.gz"

To restore all your database(s) and information to MySQL just run this following command:

mysql -u root < allmysqldatabases.sql

 

Restoring a specific MySQL database/script backup to a specific database

mysql -u $username --password="$password" --database="$dbname" < $sqlscript
 

 

Issues/Problems/Complications with Restoring MySQL Databases

One issue I have is that despite backing up and restoring the database "mysql" I cannot authenticate successfully with my old login information.  I don't know why because a query of the mysql table shows the usernames I am expecting to see.

I'll have to research more on this point, but the good news is that all the other tables/databases and entries are there as expected.
 

*UPDATE

I knew everything was there and that the "mysql" table was fully populated.  All I had to do was restart mysql server (mysqld) and everything worked as normal with the old passwords and privileges I originally had.


Tags:

mysql, restoring, dumping, backing, databasesbacking, databases, mysqldump, admin, allmysqldatabases, sql, quot, clause, isn, login, user, output, database, redirection, writes, info, redirecting, ll, gibberish, assumes, method, migrate, gzip, corruption, quicker, compressing, reduces, originally, mb, dumps, gz, compressed, format, mysqlbackup, specifies, specified, someothedb, someotherdb, databasename, tablename, thetable, specifying, db, default, populated, restore, tricky, scenarios, server, install, previous, ve, username, password, dbname, sqlscript, complications, authenticate, successfully, query, usernames, entries, update, restart, mysqld, passwords, privileges,

Latest Articles

  • How To Upgrade Debian 8,9,10 to Debian 12 Bookworm
  • Linux dhcp dhclient Mint Redhat Ubuntu Debian How To Use Local Domain DNS Server Instead of ISPs
  • Docker dockerd swarm high CPU usage cause solution
  • Docker Minimum Requirements/How Efficient is Docker? How Much Memory Does Dockerd Use?
  • qemu-nbd: Failed to set NBD socket solution qemu-nbd: Disconnect client, due to: Failed to read request: Unexpected end-of-file before all bytes were read
  • apache2 httpd apache server will not start [pid 22449:tid 139972160445760] AH00052: child pid 23248 exit signal Aborted (6) solution Mint Debian Ubuntu Redhat
  • How to use the FTDI USB serial cable to RJ45 adapter to connect to the console on Cisco/Juniper Switch Router Firewall in Linux Ubuntu Debian Redhat
  • How To Setup Python3 in Ubuntu Docker Image for AI Deep Learning
  • How to Configure NVIDIA GPUs with Docker on Ubuntu: A Comprehensive Guide for AI Deep Learning CUDA Solution
  • Linux Ubuntu Mint how to check nameservers when /etc/resolv.conf disabled solution
  • Docker cannot work on other overlayfs filesystems such as ecryptfs won't start overlayfs: filesystem on '/home/docker/overlay2/check-overlayfs-support130645871/upper' not supported as upperdir
  • Linux How To Access Original Contents of Directory Mounted Debian Mint CentOS Redhat Solution
  • ecryptfs how to manually encrypt your existing home directory or other directory
  • How to Reset CIPC Cisco IP Communicator for CME CUCM CallManager
  • Internet Explorer Cannot Download File "Your security settings do not allow for this file to be downloaded." Security Settings Solution
  • Linux How To Upgrade To The Latest Kernel Debian Mint Ubuntu
  • Firefox how to restore and backup saved passwords and history which files/location
  • Linux How To echo as root solution to use tee permission denied solution Ubuntu Debian Mint Redhat CentOS
  • Linux how to keep command line bash process running if you are disconnected or need to logout of SSH remotely
  • Linux swapping too much? How to check the swappiness and stop swapping