MYSQL DBA Queries

​​1) To find the version.

SHOW VARIABLES LIKE "%version%";

2) To update the password for root user.

use mysql;

update user set password=PASSWORD("fire2013") where User='root';

flush privileges;

3) To create Database.

create database bugs;

4) To create User (% implies access from any system,localhost/127.0.0.1 for access from base instance)

create user 'bugs'@'%' IDENTIFIED BY 'fire2013';

5) Grant permission to User on particular database.

GRANT SELECT, INSERT,

UPDATE, DELETE, INDEX, ALTER, CREATE, LOCK TABLES,

CREATE TEMPORARY TABLES, DROP, REFERENCES ON bugs.*

TO bugs@localhost IDENTIFIED BY 'fire2013';

GRANT SELECT, INSERT, UPDATE, DELETE, INDEX, ALTER, CREATE, LOCK TABLES, CREATE TEMPORARY TABLES, DROP, REFERENCES ON bugs.* TO bugs@localhost IDENTIFIED BY 'fire2013';

GRANT SELECT, INSERT, UPDATE, DELETE, INDEX, ALTER, CREATE, LOCK TABLES, CREATE TEMPORARY TABLES, DROP, REFERENCES ON bugs.* TO bugs'@'%' IDENTIFIED BY 'fire2013';

grant all privileges on bug.* to 'bugs'@'%' with grant option;

6) To delete the user

drop user bug;

drop user 'bugs'@'localhost';

7) To list the database available on it

show databases;

8) To list the user available on it.

select user from mysql.user;

select user,Host from mysql.user;

9) Data load from text file

LOAD DATA LOCAL INFILE '/tmp/testload.txt' into table testload FIELDS TERMINATED BY ',' (a,b);

LOAD DATA LOCAL INFILE "/tmp/load_test.csv" INTO TABLE loadtest.load_test FIELDS TERMINATED BY ',' IGNORE 1 LINES (id,Name);

10) To clear the screen

cls scr;

11) To Set Super privileges

UPDATE mysql.user SET Super_Priv='Y' WHERE user='root' AND host='%';

11) Database backup

# mysqldump -u root -p****** rsyslog > rsyslog.sql

12) Multiple Databases backup

# mysqldump -u root -p****** --databases rsyslog syslog > rsyslog_syslog.sql

13) All Databases backup

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

14) Backup Database Structure Only

# mysqldump -u root -p****** -–no-data rsyslog > rsyslog_structure.sql

15) Backup Database Data Only

# mysqldump -u root -p****** --no-create-db --no-create-info rsyslog > rsyslog_data.sql

16) Backup Single Table of Database

Take backup of wp_posts table from the database wordpress.

# mysqldump -u root -p****** wordpress wp_posts > wordpress_posts.sql

17) Backup Multiple Tables of Database

# mysqldump -u root -p****** wordpress wp_posts wp_comments > wordpress_posts_comments.sql

18) Backup Remote MySQL Database

The below command takes the backup of remote server [172.16.25.126] database [gallery] into a local server.

# mysqldump -h 172.16.25.126 -u root -p****** gallery > gallery.sql

Restore MySQL Database

# # mysql -u [username] –p[password] [database_name] < [dump_file.sql]

19) Restore Single MySQL Database

# mysql -u root -p****** rsyslog < rsyslog.sql

If you want to restore a database that already exist on targeted machine, then you will need to use the mysqlimport command.

# mysqlimport -u root -p****** rsyslog < rsyslog.sql

In the same way you can also restore database tables, structures and data. If you liked this article, then do share it with your friends.