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

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"

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

 

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.


  • css responsive images
  • responsive table without changing much code solution
  • yum how to install old obsolete packages
  • PHP Howto Store Value of Included File Output Into Variable
  • PHP Migration from 5.3 to 5.4+ and dealing with deprecated functions
  • ffmpeg vidstab to stabilize video
  • userdel user userdel: cannot lock /etc/passwd; try again later.
  • mdadm how to mount inactive array
  • How to find and mount mdadm arrays automatically
  • M2Crypto.SSL.Checker.WrongHost: Peer certificate subjectAltName does not match host, expected fedora-archive.ip-connect.vn.ua, got DNS:mirror.ip-connect.vn.ua
  • [Wed Sep 20 15:34:44 2017] [notice] suEXEC mechanism enabled (wrapper: /usr/sbin/suexec) [Wed Sep 20 15:34:44 2017] [error] Init: Unable to read server certificate from file /www/ssl-certs/server.crt [Wed Sep 20 15:34:44 2017] [error] SSL Library Err
  • linux how to answer yes to copy
  • linux cp and mv will not overwrite due to alias!
  • ERROR 2006 (HY000) at line 567: MySQL server has gone away
  • vbulletin 4.2.5 after upgrading from 3.6 white screen fatal php errors
  • iptables v1.4.7: can't initialize iptables table `NAT': Table does not exist (do you need to insmod?) Perhaps iptables or your kernel needs to be upgraded.
  • Linux and FreeBSD how to set time and date
  • FreeBSD/OpenBSD OpenVPN Client error "Cannot allocate TUN/TAP dev dynamically"
  • kdenlive - No LADSPA plugins were found! Check your LADSPA_PATH environment variable. [producer_xml] failed to load transition "qtblend"
  • /usr/bin/supermin-helper exited with error status 1. To see full error messages you may need to enable debugging. See http://libguestfs.org/guestfs-faq.1.html#debugging-libguestfs at /usr/bin/virt-list-partitions line 177.