How to Back Up and Restore a MySQL Database

 Back up From the Command Line (using mysqldump)

If you have shell or telnet access to your web server, you can backup your MySQL data by using the mysqldump command. This command connects to the MySQL server and creates an SQL dump file. The dump file contains the SQL statements necessary to re-create the database. Here is the proper syntax:

$ mysqldump --opt -u [uname] -p[pass] [dbname] > [backupfile.sql]

• [uname] Your database username
• [pass] The password for your database (note there is no space between -p and the password)
• [dbname] The name of your database
• [backupfile.sql] The filename for your database backup
• [--opt] The mysqldump option

For example, to backup a database named 'Tutorials' with the username 'root' and with no password to a file tut_backup.sql, you should accomplish this command:

$ mysqldump -u root -p Tutorials > tut_backup.sql

This command will backup the 'Tutorials' database into a file called tut_backup.sql which will contain all the SQL statements needed to re-create the database.

With mysqldump command you can specify certain tables of your database you want to backup. For example, to back up only php_tutorials and asp_tutorials tables from the 'Tutorials' database accomplish the command below. Each table name has to be separated by space.

$ mysqldump -u root -p Tutorials php_tutorials asp_tutorials > tut_backup.sql

Sometimes it is necessary to back up more that one database at once. In this case you can use the --database option followed by the list of databases you would like to backup. Each database name has to be separated by space.

$ mysqldump -u root -p --databases Tutorials Articles Comments > content_backup.sql

If you want to back up all the databases in the server at one time you should use the --all-databases option. It tells MySQL to dump all the databases it has in storage.

$ mysqldump -u root -p --all-databases > alldb_backup.sql

The mysqldump command has also some other useful options:

--add-drop-table: Tells MySQL to add a DROP TABLE statement before each CREATE TABLE in the dump.

--no-data: Dumps only the database structure, not the contents.

--add-locks: Adds the LOCK TABLES and UNLOCK TABLES statements you can see in the dump file.

The mysqldump command has advantages and disadvantages. The advantages of using mysqldump are that it is simple to use and it takes care of table locking issues for you. The disadvantage is that the command locks tables. If the size of your tables is very big mysqldump can lock out users for a long period of time.

Back up your MySQL Database with Compress

If your mysql database is very big, you might want to compress the output of mysqldump. Just use the mysql backup command below and pipe the output to gzip, then you will get the output as gzip file.

$ mysqldump -u [uname] -p[pass] [dbname] | gzip -9 > [backupfile.sql.gz]

If you want to extract the .gz file, use the command below:

$ gunzip [backupfile.sql.gz]

Restoring your MySQL Database

Above we backup the Tutorials database into tut_backup.sql file. To re-create the Tutorials database you should follow two steps:

• Create an appropriately named database on the target machine
• Load the file using the mysql command:

$ mysql -u [uname] -p[pass] [db_to_restore] < [backupfile.sql]

Have a look how you can restore your tut_backup.sql file to the Tutorials database.

$ mysql -u root -p Tutorials < tut_backup.sql

To restore compressed backup files you can do the following:

gunzip < [backupfile.sql.gz] | mysql -u [uname] -p[pass] [dbname]

If you need to restore a database that already exists, you'll need to use mysqlimport command. The syntax for mysqlimport is as follows:

mysqlimport -u [uname] -p[pass] [dbname] [backupfile.sql]

How to find MySQL Server port Number

mysql> SHOW GLOBAL VARIABLES LIKE 'PORT';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port          | 3306  |
+---------------+-------+
1 row in set (0.00 sec)

mysql>

ERROR 1698 (28000): Access denied for user 'root'@'localhost' - Mysql Server

I was using ubuntu 18 and simply installed MySQL (password:root) with the following commands.

sudo apt install mysql-server
sudo mysql_secure_installation

When I tried to log in with the normal ubuntu user it was throwing me this issue.

ERROR 1698 (28000): Access denied for user 'root'@'localhost'

But I was able to login to MySQL via the super user. Using the following commands I was able to log in via a normal user.

sudo mysql

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'root';
exit;

Then you should be able to login to Mysql with the normal account.

How to start/stop mysql server

nagaraju@nagaraju:/usr/local/softwares/apache-hive-2.3.6-bin/bin$ service mysql status
● mysql.service - MySQL Community Server
   Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
   Active: active (running) since Wed 2020-03-11 16:08:37 IST; 27min ago
 Main PID: 7981 (mysqld)
    Tasks: 27 (limit: 3227)
   CGroup: /system.slice/mysql.service
           └─7981 /usr/sbin/mysqld --daemonize --pid-file=/run/mysqld/mysqld.pid

Mar 11 16:08:36 nagaraju systemd[1]: Starting MySQL Community Server...
Mar 11 16:08:37 nagaraju systemd[1]: Started MySQL Community Server.
nagaraju@nagaraju:/usr/local/softwares/apache-hive-2.3.6-bin/bin$ service mysql stop
nagaraju@nagaraju:/usr/local/softwares/apache-hive-2.3.6-bin/bin$ service mysql status
● mysql.service - MySQL Community Server
   Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
   Active: inactive (dead) since Wed 2020-03-11 16:36:35 IST; 7s ago
 Main PID: 7981 (code=exited, status=0/SUCCESS)

Mar 11 16:08:36 nagaraju systemd[1]: Starting MySQL Community Server...
Mar 11 16:08:37 nagaraju systemd[1]: Started MySQL Community Server.
Mar 11 16:36:33 nagaraju systemd[1]: Stopping MySQL Community Server...
Mar 11 16:36:35 nagaraju systemd[1]: Stopped MySQL Community Server.
nagaraju@nagaraju:/usr/local/softwares/apache-hive-2.3.6-bin/bin$ service mysql start
nagaraju@nagaraju:/usr/local/softwares/apache-hive-2.3.6-bin/bin$ service mysql status
● mysql.service - MySQL Community Server
   Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
   Active: active (running) since Wed 2020-03-11 16:37:00 IST; 5s ago
  Process: 9596 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/run/mysqld/mysqld.pid (code=exited, status=0/SUCCESS)
  Process: 9587 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code=exited, status=0/SUCCESS)
 Main PID: 9598 (mysqld)
    Tasks: 27 (limit: 3227)
   CGroup: /system.slice/mysql.service
           └─9598 /usr/sbin/mysqld --daemonize --pid-file=/run/mysqld/mysqld.pid

Mar 11 16:36:57 nagaraju systemd[1]: Starting MySQL Community Server...
Mar 11 16:37:00 nagaraju systemd[1]: Started MySQL Community Server.
nagaraju@nagaraju:/usr/local/softwares/apache-hive-2.3.6-bin/bin$ 

 

User creation on MySQL and giving Privileges

mysql> CREATE USER 'good'@'localhost' IDENTIFIED BY 'man';

Query OK, 0 rows affected (0.16 sec)

mysql> GRANT ALL ON iot.* TO 'good'@'localhost';

Query OK, 0 rows affected (0.18 sec)

mysql> FLUSH PRIVILEGES;

Query OK, 0 rows affected (0.12 sec)

-------------------------------------------------------------------------------------------------------------------------

Here localhost means that  Data Pushing Application and MySQL DB Server both are on the same System.

--------------------------------------------------------------------------------------------------------------------------

If Data Pushing Application  and MySQL DB Server are on different Systems.

Then we give  the permission to that  Data Pushing Application like ..

CREATE USER 'good'@’2.16.1.200’ IDENTIFIED BY 'man';

GRANT ALL ON iot.* TO 'good'@'2.16.1.200';   (Here 2.16.1.200 is the Data Pushing Application IP Address.)

---------------------------------------------------------------------------------------------------------------------------------------------------------------------

If we want to access any host(IP Address of any System) then

 CREATE USER 'good'@’%’ IDENTIFIED BY 'man';

GRANT ALL ON iot.* TO 'good'@'%';   (Here % means that Any host can access our MySQL Server DB)

-------------------------------------------------------------------------------------------------------------------------

Login to MySQL Server in Ubuntu 18.0 .

Login to MySQL Server in Ubuntu 18.0 .

In the latest versions of the MySQL Server in Ubunte 18.0 .
Login to MySQL Server process is changed .
So, first we have to find the password  in the following way and then need to login to the MySQL Server.


nagaraju@nagaraju:~$ sudo cat /etc/mysql/debian.cnf
# Automatically generated for Debian scripts. DO NOT TOUCH!
[client]
host     = localhost
user     = debian-sys-maint
password = LnW2fO2BdFpjj62L
socket   = /var/run/mysqld/mysqld.sock
[mysql_upgrade]
host     = localhost
user     = debian-sys-maint
password = GoodMan
socket   = /var/run/mysqld/mysqld.sock

nagaraju@nagaraju:~$ mysql -u debian-sys-maint -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.27-0ubuntu0.18.04.1 (Ubuntu)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.


mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql>