MYSQL/MARIA DB SCRIPTS

The below scripts will work for both mysql and maridb . 

Please write in comment section if any of the links are not working

 

DATABASE MANAGEMENT
Create database in mysql
View database list in mysql
How to connect to mysql db
Drop a database in mysql
Find database sizes in mysql
How to find timezone info
How to find MySQL version
Find current sessions in MySQL
How to kill a session in mysql
How to kill all sessions of an user
How to find startup time and uptime MySQL
How to find data directory location
Find current timestamp info mysql
Find MySQL configuration values
Find mysql db service restart time
List down storage engines

USER MANAGEMENT
List users present in mysql
Create/drop user in mysql
Alter an user in mysql
How to convert a user to superuser
Grant privileges to user in mysql
REVOKE privilege commands

BACKUP/RECOVERY
Backup a mysql database
Restore a mysql database from backup
Backup multiple mysql databases
Backup a specific table
Restore all databases
Backup databases in compress format

DB MANAGEMENT

TABLESPACE MANAGEMENT

OBJECT MANAGEMENT

 

AUDITING & SECURITY

NETWORK

 

REPLICATION

OTHERS

 
    1. This page will be updated regularly with new scripts.Please provide suggestions to improve this page.

Keep visiting us.

##Contributed by - Vasudeva Rao
Create a database in MySQL / MariaDB

-- Below commands can be used to create database

MariaDB [(none)]> CREATE DATABASE testdb;
Query OK, 1 row affected (0.009 sec)

MariaDB [(none)]> create database testdb1 character set UTF8mb4 collate utf8mb4_bin;
Query OK, 1 row affected (0.009 sec)

-- View the create database statement used for creating db 

MariaDB [(none)]> SHOW CREATE DATABASE testdb1;
+----------+-----------------------------------------------------------------------------------------+
| Database | Create Database |
+----------+-----------------------------------------------------------------------------------------+
| testdb1 | CREATE DATABASE `testdb1` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin */ |
+----------+-----------------------------------------------------------------------------------------+
1 row in set (0.007 sec)

##Contributed by - Vasudeva Rao

View database list

MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| testdb |
+--------------------+
5 rows in set (0.006 sec)

mysql> SELECT * fROM information_schema.schemata;
+--------------+--------------------+----------------------------+------------------------+----------+--------------------+
| CATALOG_NAME | SCHEMA_NAME | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | SQL_PATH | DEFAULT_ENCRYPTION |
+--------------+--------------------+----------------------------+------------------------+----------+--------------------+
| def          | mysql               | utf8mb4           | utf8mb4_0900_ai_ci | NULL | NO |
| def          | information_schema  | utf8             | utf8_general_ci | NULL | NO |
| def.         | performance_schema  | utf8mb4           | utf8mb4_0900_ai_ci | NULL | NO |
| def          | sys                 | utf8mb4     | utf8mb4_0900_ai_ci | NULL | NO |
| def          | testdb             | utf8mb4 | utf8mb4_0900_ai_ci | NULL | NO |
| def          | test               | utf8mb4 | utf8mb4_0900_ai_ci | NULL | NO |
+--------------+--------------------+----------------------------+------------------------+----------+--------------------+
6 rows in set (0.00 sec)

-- database list that match a pattern

mysql> show databases like 'test%';
+------------------+
| Database (test%) |
+------------------+
| test |
| testdb |
+------------------+
2 rows in set (0.00 sec)

##Contributed by - Vasudeva Rao
How to connect to MySQL / MariaDB database

hostname$ export PATH=/usr/local/mysql/bin:$PATH
hostname$ which mysql
/usr/local/mysql/bin

SYNTAX - mysql -u user -p

C:\Program Files\MariaDB 10.4\bin>mysql -u root -p
Enter password: ****
Welcome to the MariaDB monitor. Commands end with ; or \g.Your MariaDB connection id is 19.Server version: 10.4.18-MariaDB 

Find current connection info:

MariaDB [(none)]> \s
--------------
mysql Ver 15.1 Distrib 10.4.18-MariaDB, for Win64 (AMD64), source revision 53123dfa3e365138591fd2f160c6057aca00a3e6

Connection id: 19
Current database:
Current user: root@localhost
SSL: Not in use
Using delimiter: ;
Server: MariaDB
Server version: 10.4.18-MariaDB mariadb.org binary distribution
........

Below is the alternative command:

MariaDB [(none)]> status;

Get current user and current database:

mariadb> select current_user,database();
+----------------+------------+
| current_user  | database() |
+----------------+------------+
| root@localhost | test |
+----------------+------------+
1 row in set (0.00 sec)

switch to another database:

MariaDB> USE TEST;
Database changed

MariaDB> select database();
+------------+
| database() |
+------------+
| test |
+------------+
1 row in set (0.00 sec)

##Contributed by - Vasudeva Rao
Drop database from mysql /MariaDB

C:\Program Files\MariaDB 10.4\bin>mysql -u root -p
Enter password: ****

-- List down databases
MariaDB [(none)]> show databases;
+--------------------+
| Database.          |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
| testdb             |
| testdb1            |
+--------------------+
6 rows in set (0.001 sec)

-- Check the status:
MariaDB [(none)]> status

-- Drop database:
MariaDB [(none)]> DROP DATABASE testdb;
Query OK, 0 rows affected (0.022 sec)

MariaDB [(none)]> show databases like 'test%';
+--------------------+
| Database.          |
+--------------------+
| test               |
| testdb1            |
+--------------------+
5 rows in set (0.001 sec)

MariaDB [(none)]> SELECT schema_name FROM information_schema.schemata;

Contributed by - Vasudeva Rao

How to get MySQL / MariaDB db size:

MariaDB [(none)]> SELECT table_schema AS "Database", SUM(data_length + index_length) / 1024 / 1024 / 1024 AS "Size (GB)" FROM information_schema.TABLES GROUP BY table_schema;
+--------------------+----------------+
| Database           | Size (GB) |
+--------------------+----------------+
| information_schema | 0.000198364258 |
| mysql              | 0.002159118652 |
| performance_schema | 0.000000000000 |
| va                 | 0.000030517578 |
| vasu               | 0.000061035156 |
+--------------------+----------------+
5 rows in set (1.564 sec)

MariaDB [(none)]> SELECT table_schema AS "Database", SUM(data_length + index_length) / 1024 / 1024 AS "Size (MB)" FROM information_schema.TABLES GROUP BY table_schema;
+--------------------+------------+
| Database           | Size (MB) |
+--------------------+------------+
| information_schema | 0.20312500 |
| mysql              | 2.21093750 |
| performance_schema | 0.00000000 |
| va                 | 0.03125000 |
| vasu               | 0.06250000 |
+--------------------+------------+
5 rows in set (0.345 sec)

##Contributed by - Vasudeva Rao
How to find timezone info

Check whether time_zone table is updated or not.
MariaDB [(none)]> select * from mysql.time_zone_name;
Empty set (0.000 sec)

MariaDB [mysql]> select DATE_FORMAT(CONVERT_TZ(FROM_UNIXTIME(unix_timestamp()), 'GMT', 'Europe/Dublin'), '%Y%u');
+------------------------------------------------------------------------------------------+
| DATE_FORMAT(CONVERT_TZ(FROM_UNIXTIME(unix_timestamp()), 'GMT', 'Europe/Dublin'), '%Y%u') |
+------------------------------------------------------------------------------------------+
| NULL |
+------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

Here we have seen NULL value means , there is no meta data information about time_zone_name table

If we see 0 Rows in Linux Mysql / MariaDB , we have to run the below command to populate in mysql schema

$ /usr/local/mysql/bin/mysql_tzinfo_to_sql /usr/share/zoneinfo | /usr/local/mysql/bin/mysql -u root mysql -p

After this you can select once again mysql.time_zone_name table , we can see 1700+ Rows.

MariaDB [mysql]> select count(1) from time_zone_name;
+----------+
| count(1) |
+----------+
| 1765 |
+----------+
1 row in set (0.001 sec)

-- Testing after populate the metadata table

MariaDB [mysql]> select DATE_FORMAT(CONVERT_TZ(FROM_UNIXTIME(unix_timestamp()), 'GMT', 'Europe/Dublin'), '%Y%u');
+------------------------------------------------------------------------------------------+
| DATE_FORMAT(CONVERT_TZ(FROM_UNIXTIME(unix_timestamp()), 'GMT', 'Europe/Dublin'), '%Y%u') |
+------------------------------------------------------------------------------------------+
| 202120 |
+------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

MariaDB [(none)]> SELECT @@global.time_zone, @@session.time_zone;
+--------------------+---------------------+
| @@global.time_zone | @@session.time_zone |
+--------------------+---------------------+
| SYSTEM | SYSTEM |
+--------------------+---------------------+
1 row in set (0.006 sec)

MariaDB [(none)]> SELECT @@global.time_zone;
+--------------------+
| @@global.time_zone |
+--------------------+
| SYSTEM |
+--------------------+
1 row in set (0.001 sec)

MariaDB [(none)]> select NOW();
+---------------------+
| NOW() |
+---------------------+
| 2021-07-01 11:42:38 |
+---------------------+
1 row in set (0.000 sec)

##Contributed by - Vasudeva Rao

Below commands can be used to find MySQL / MariaDB version:

MariaDB [(none)]> select version();
+-----------------+
| version() |
+-----------------+
| 10.4.18-MariaDB |
+-----------------+
1 row in set (0.000 sec)

$ mysql --version
mysql Ver 15.1 Distrib 10.4.18-MariaDB, for Win64 (AMD64), source revision 53123dfa3e365138591fd2f160c6057aca00a3e6

mysql> SHOW VARIABLES LIKE "%version%";
+--------------------------+-------------------------------+
| Variable_name            | Value                         |
+--------------------------+-------------------------------+
| admin_tls_version        | TLSv1,TLSv1.1,TLSv1.2,TLSv1.3 |
| immediate_server_version | 999999                        |
| innodb_version           | 8.0.25                        |
| original_server_version  | 999999                        |
| protocol_version         | 10                            |
| slave_type_conversions   |                               |
| tls_version              | TLSv1,TLSv1.1,TLSv1.2,TLSv1.3 |
| version                  | 8.0.25                        |
| version_comment          | MySQL Community Server - GPL  |
| version_compile_machine  | x86_64                        |
| version_compile_os       | macos11                       |
| version_compile_zlib     | 1.2.11                        |
+--------------------------+-------------------------------+
12 rows in set (0.04 sec)

Contributed by - Vasudeva Rao
Find current sessions/process in MYSQL/MARIA DB

Show all processes in mysql cluster.

mysql> show processlist;
+----+-----------------+-----------+------+---------+-------+------------------------+------------------+
| Id | User            | Host       | db  | Command | Time  | State                  | Info |
+----+-----------------+-----------+------+---------+-------+------------------------+------------------+
| 5  | event_scheduler | localhost | NULL | Daemon | 34888 | Waiting on empty queue | NULL |
| 23 | root            | localhost | test | Sleep. | 36    | | NULL                 |
| 24 | root.           | localhost | NULL | Query  | 0      | init                   | show processlist |
+----+-----------------+-----------+------+---------+-------+------------------------+------------------+
3 rows in set (0.00 sec)

Show processes for a specific user:

mysql> select * from information_schema.processlist where user='root'\G

Show processes for a particular database:

mysql> select * from information_schema.processlist where DB='test';
+----+------+-----------+------+---------+------+-------+------+
| ID | USER | HOST | DB | COMMAND | TIME | STATE | INFO |
+----+------+-----------+------+---------+------+-------+------+
| 23 | root | localhost | test | Sleep | 70 | | NULL |
+----+------+-----------+------+---------+------+-------+------+
1 row in set (0.00 sec)

##Contributed by - Vasudeva Rao

Get the processid for the session:

mysql> show processlist;
+----+-----------------+-----------+------+---------+-------+------------------------+------------------+
| Id | User            | Host.     | db   | Command | Time  | State                  | Info |
+----+-----------------+-----------+------+---------+-------+------------------------+------------------+
| 5 | event_scheduler  | localhost  | NULL | Daemon  | 35192 | Waiting on empty queue | NULL |
| 24 | root            | localhost  | NULL | Query   | 0     | init                   | show processlist |
| 25 | root            | localhost  | test | Sleep.   | 6    |                        | NULL         |
+----+-----------------+-----------+------+---------+-------+------------------------+------------------+
3 rows in set (0.00 sec)

mysql> kill 25;

Query OK, 0 rows affected (0.00 sec)

mysql> show processlist;
+----+-----------------+-----------+------+---------+-------+------------------------+------------------+
| Id | User            | Host.     | db   | Command | Time  | State                  | Info |
+----+-----------------+-----------+------+---------+-------+------------------------+------------------+
| 5 | event_scheduler  | localhost  | NULL | Daemon  | 35192 | Waiting on empty queue | NULL |
| 24 | root            | localhost  | NULL | Query   | 0     | init                   | show processlist |
mysql>

 

 

How to kill all sessions of an user

mysql> show processlist;
+----+-----------------+-----------+--------+---------+-------+------------------------+------------------+
| Id | User            | Host      | db     | Command | Time   | State                  | Info |
+----+-----------------+-----------+--------+---------+-------+------------------------+------------------+
| 5 | event_scheduler  | localhost | NULL  | Daemon   | 36418 | Waiting on empty queue | NULL |
| 24 | root | localhost | NULL     | Query | 0        | init  | show processlist        |
| 26 | root | localhost | test     | Sleep | 376      |       | NULL                    |
| 27 | root | localhost | testdb.  | Sleep | 347      |       | NULL                    |
+----+-----------------+-----------+--------+---------+-------+------------------------+------------------+
4 rows in set (0.00 sec)

Generate kill statements for killing all sessions of user root

mysql> SELECT GROUP_CONCAT(CONCAT('KILL ',id,';') SEPARATOR ' ') 'Run the kill statements ----->>> ' FROM information_schema.processlist WHERE user='root'\G
*************************** 1. row ***************************
Run the kill statements ----->>> : KILL 24; KILL 26; KILL 27;
1 row in set (0.00 sec)

mysql> KILL 24; KILL 26; KILL 27;
ERROR 1317 (70100): Query execution was interrupted
ERROR 2013 (HY000): Lost connection to MySQL server during query
No connection. Trying to reconnect...
Connection id: 28
Current database: *** NONE ***

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

##Contributed by - Vasudeva Rao
Uptime of server

MariaDB [(none)]> status;
--------------
mysql Ver 15.1 Distrib 10.4.18-MariaDB, for Win64 (AMD64), source revision 53123dfa3e365138591fd2f160c6057aca00a3e6

Connection id: 26
Current database:
.............
TCP port: 3306
Uptime: 4 days 19 hours 28 min 14 sec

Alternative command

MariaDB [(none)]> \s

Server startup time:

MariaDB [(none)]> select TIME_FORMAT(SEC_TO_TIME(VARIABLE_VALUE ),'%Hh %im') as Uptime from information_schema.GLOBAL_STATUS where VARIABLE_NAME='Uptime';
+----------+
| Uptime.  |
+----------+m
| 115h 30m |
+----------+
1 row in set (0.007 sec)

C:\Program Files\MariaDB 10.4\bin>mysqladmin version -u root -p
Enter password: ****
mysqladmin Ver 9.1 Distrib 10.4.18-MariaDB, for Win64 on AMD64 Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Server version 10.4.18-MariaDB
Protocol version 10
Connection localhost via TCP/IP
TCP port 3306
Uptime: 4 days 19 hours 33 min 2 sec

 

##Contributed by - Vasudeva Rao

MariaDB [(none)]> show variables like '%data%';
+---------------------------------------------+-------------------------------------+
| Variable_name                               | Value                               |
+---------------------------------------------+-------------------------------------+
| datadir                                     | /usr/local/mysql/data/ |

MariaDB [(none)]> SELECT Variable_Value FROM information_schema.GLOBAL_VARIABLES WHERE Variable_Name = 'datadir';
+-------------------------------------+
| Variable_Value                      |
+-------------------------------------+
| /usr/local/mysql/data/ |
+-------------------------------------+
1 row in set (0.001 sec)

mysql> select @@datadir;
+------------------------+
| @@datadir              |
+------------------------+
| /usr/local/mysql/data/ |
+------------------------+
1 row in set (0.00 sec)

##Contributed by - Vasudeva Rao

 Find current data/time on MySQL / MariaDB

mysql> select CURRENT_TIMESTAMP,current_date,localtime(0),localtimestamp(0);
+---------------------+--------------+---------------------+---------------------+
| CURRENT_TIMESTAMP   | current_date | localtime(0)        | localtimestamp(0) |
+---------------------+--------------+---------------------+---------------------+
| 2021-07-04 22:44:33 | 2021-07-04   | 2021-07-04 22:44:33 | 2021-07-04 22:44:33 |
+---------------------+--------------+---------------------+---------------------+
1 row in set (0.00 sec)

##Contributed by - Vasudeva Rao

Find MySQL / MariaDB configuration values

1 . Get Config values from mysql prompt.

MariaDB [(none)]> SELECT VARIABLE_NAME,VARIABLE_VALUE FROM information_schema.GLOBAL_VARIABLES;

MariaDB [(none)]> SELECT VARIABLE_NAME,VARIABLE_VALUE FROM information_schema.GLOBAL_VARIABLES WHERE Variable_Name = 'port';
+---------------+----------------+
| VARIABLE_NAME | VARIABLE_VALUE |
+---------------+----------------+
| PORT          | 3306           |
+---------------+----------------+
1 row in set (0.001 sec)

MariaDB [(none)]> SELECT VARIABLE_NAME,VARIABLE_VALUE FROM information_schema.GLOBAL_VARIABLES WHERE Variable_Name = 'datadir';
+---------------+-------------------------------------+
| VARIABLE_NAME | VARIABLE_VALUE                      |
+---------------+-------------------------------------+
| DATADIR       | C:\Program Files\MariaDB 10.4\data\ |
+---------------+-------------------------------------+
1 row in set (0.001 sec)

2. Alternatively you can check my.cnf file in /etc folder in Linux

cat /etc/my.cnf

##Contributed by - Vasudeva Rao

Find the last MySQL / MariaDB Database service restarted / server reboot time

MariaDB [(none)]> status
--------------
mysql Ver 15.1 Distrib 10.4.18-MariaDB, for Win64 (AMD64), source revision 53123dfa3e365138591fd2f160c6057aca00a3e6

Connection id: 33
Current database:
Current user: root@localhost
SSL: Not in use
...........
Uptime: 6 days 2 hours 3 min 3 sec

-- In older version:

MariaDB [(none)]> select TIME_FORMAT(SEC_TO_TIME(VARIABLE_VALUE ),'%Hh %im') as Uptime from information_schema.GLOBAL_STATUS where VARIABLE_NAME='Uptime';
+----------+
| Uptime   |
+----------+
| 146h 03m |
+----------+
1 row in set (0.009 sec)

-- In latest mysql version:

MariaDB [(none)]> SHOW GLOBAL STATUS LIKE 'Uptime';
+---------------+--------+
| Variable_name | Value |
+---------------+--------+
| Uptime        | 525859 |
+---------------+--------+
1 row in set (0.001 sec)

##Contributed by - Vasudeva Rao
mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+-----
| Engine             | Support | Comment                                                 | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+----
| ARCHIVE            | YES     | Archive storage engine                                        | NO | NO | NO |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO | NO | NO |
| FEDERATED          | NO      | Federated MySQL storage engine                                | NULL | NULL | NULL |
| MyISAM             | YES.    | MyISAM storage engine                                         | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                            | NO | NO | NO |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys    | YES | YES | YES |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables.    | NO | NO | NO |
| CSV                | YES     | CSV storage engine                                             | NO | NO | NO |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.01 sec)

##Contributed by - Vasudeva Rao

-- List all users present in mysql

mysql> select user,host,account_locked,password_expired from mysql.user;
+------------------+-----------+----------------+------------------+
| user             | host      | account_locked | password_expired |
+------------------+-----------+----------------+------------------+
| mysql.infoschema | localhost | Y              | N |
| mysql.session.   | localhost | Y              | N |
| mysql.sys        | localhost | Y              | N |
| root             | localhost | N              | N |
+------------------+-----------+----------------+------------------+
4 rows in set (0.00 sec)

##Contributed by - Vasudeva Rao
Create user in mysql:

mysql> CREATE USER 'test_user'@'%' IDENTIFIED BY 'test_user';
Query OK, 0 rows affected (0.02 sec)

mysql> GRANT ALL ON *.* TO 'user'@'localhost';
Query OK, 0 rows affected (0.01 sec)

View create user statements:
MariaDB [(none)]> SHOW CREATE USER 'test_user'@'localhost';

MariaDB [(none)]> SHOW CREATE USER 'test_user'@'%';

Create user with password expire:

MariaDB [(none)]> CREATE USER 'test_user2'@'localhost' IDENTIFIED BY 'test_user' PASSWORD EXPIRE;
Query OK, 0 rows affected (0.004 sec)

Drop user:

MariaDB [(none)]> drop user 'test_user'@'localhost';
Query OK, 0 rows affected (0.009 sec)

##Contributed by - Vasudeva Rao

Rename a user:

mysql> RENAME USER 'dbatest' TO 'dbaprod';
Query OK, 0 rows affected (0.02 sec)

change password of a user:

mysql> ALTER USER 'dbaprod'@'%' IDENTIFIED BY 'dbaprod';
Query OK, 0 rows affected (0.01 sec)

Change resource option:

mysql> alter user 'dbaprod'@'%' with MAX_USER_CONNECTIONS 10;
Query OK, 0 rows affected (0.01 sec)

Lock/unlock an account:

mysql> alter user 'dbaprod'@'%' account lock;
Query OK, 0 rows affected (0.01 sec)

mysql> alter user 'dbaprod'@'%' account unlock;
Query OK, 0 rows affected (0.01 sec)

##Contributed by - Vasudeva Rao

Make sure you are connected with root/admin user:

mysql> select current_user();
+----------------+
| current_user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

Create normal user if not created.

mysql> CREATE user 'super'@'%' IDENTIFIED BY 'super';
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE user 'super'@'localhost' IDENTIFIED BY 'super';
Query OK, 0 rows affected (0.02 sec)

Grant privileges for making it super user:

mysql> GRANT ALL PRIVILEGES ON *.* TO 'super'@'%' WITH GRANT OPTION;
Query OK, 0 rows affected (0.03 sec)

mysql> GRANT ALL PRIVILEGES ON *.* TO 'super'@'localhost' WITH GRANT OPTION;
Query OK, 0 rows affected (0.03 sec)

Reload all the privileges

mysql> FLUSH PRIVILEGES;

Query OK, 0 rows affected (0.02 sec)

 View the grants:

mysql> show grants for super;

##Contributed by - Vasudeva Rao

Examples on GRANT command

GRANT ALL PRIVILEGES ON *.* to 'user_name'@'localhost';
GRANT ALL PRIVILEGES ON *.* to 'user_name'@'%';
GRANT ALL PRIVILEGES ON *.* to 'user_name'@'localhost' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* to 'user_name'@'%' WITH GRANT OPTION;

GRANT ALL PRIVILEGES ON dbname.* to 'user_name'@'localhost';
GRANT ALL PRIVILEGES ON dbname.* to 'user_name'@'%';

GRANT INSERT,UPDATE,DELETE ON TABLE DBNAME.TABLENAME TO 'user_name'@'localhost';
GRANT INSERT,UPDATE,DELETE ON TABLE DBNAME.TABLENAME TO 'user_name'@'%';

GRANT CREATE ON DATABASE.* to 'user_name'@'localhost';
GRANT CREATE ON DATABASE.* to 'user_name'@'%';

GRANT SELECT , EXECUTE ON DATABASE.* to 'user_name'@'%';
GRANT SELECT , EXECUTE ON DATABASE.* to 'user_name'@'localhost';

##Contributed by - Vasudeva Rao

EXAMPLE:

REVOKE privileges ON <object> FROM user;

REVOKE DELETE, UPDATE ON <table_name> FROM 'user'@'localhost';

REVOKE DELETE, UPDATE ON <table_name> FROM 'user'@'%';

REVOKE ALL ON <table_name> FROM 'user'@'localhost';

REVOKE ALL ON <table_name> FROM 'user'@'%';

REVOKE SELECT ON <object> FROM 'username'@'localhost';

REVOKE SELECT ON <object> FROM 'username'@'localhost';

##Contributed by - Vasudeva Rao

Backup a single database:

Syntax : mysqldump –u root –p[password] [database_name] > dumpfilename.sql

This example takes a backup of sampledb database and dumps the output to dbdump.sql

[mysql@localhost]# mysqldump –u root –pmysql sampledb > /tmp/dbdump.sql

##Contributed by - Vasudeva Rao

If the valid backup file is available with you, then you can restore the same using below command.

SYNTAX mysqldump –u root –p[password] [databasename] < dumpfilename.sql

-- If  dbdump.sql is the backup file , then restore command will be 

[mysql@localhost]# mysqldump –u root –pmysql sampledb > dbdump.sql

##Contributed by - Vasudeva Rao

-- Backup multiple databases

For example, if you want to take backup of both sampledb and newdb database, execute the mysqldump as shown below:

[root@localhost]# mysqldump –u root –p --databases sampled newdb > /tmp/dbdump.sql

We cannot take backup of information_schema and performance_schema databases as these are metadata databases.

-- Backup all databases:

root# mysqldump -u root -p --all-databases > all-database.sql

##Contributed by - Vasudeva Rao

SYNATX:

mysqldump –c –u username –p databasename tablename > /tmp/databasename.tablename.sql

In this example, we backup only the ta2 table from sampledb database.

root$ mysqldump –u root –p sample ta2 > /tmp/nwedb_ta2.sql

##Contributed by - Vasudeva Rao

[root]$ mysql -u root -p < /tmp/alldbs55.sql

##Contributed by - Vasudeva Rao

With bzip2:
mysqldump --all-databases | bzip2 -c > databasebackup.sql.bz2 

With gzip:
mysqldump --all-databases | gzip> databasebackup.sql.gz