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

 

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