MySQL Important Useful Commands:

1. MySQL Dump For Single Database :

# mysqldump --force --opt --single-transaction --lock-tables=false --skip-events --user=root --password=password --routines --databases shockable > /tmp/shockable.sql

2. MySQL Dump For All Databases :

# mysqldump --force --opt --single-transaction --lock-tables=false --skip-events --user=root --password=password --routines --all-databases > /tmp/all_backups.sql

3. Restore Database from MySQL Dump :

# mysql -u root -ppassword < /tmp/all_backups.sql

4. Restore a Single Database from All_Databases :

# mysql -u root -p --one-database destdbname < /tmp/all_databases.sql

5. Backup A Single Table (Table Name: users) :

# mysqldump -u root -ppassword database_name users > /tmp/users.table.sql

6. Restore that Table :

mysql> create database database_name;
mysql> exit

# mysql -u root -ppassword database_name < /tmp/users.table.sql

7. MySQL Ignore Table :

# mysqldump -u username -ppasswword --ignore-table=dbname.tablename dbname > /tmp/dbname.sql

8. Show MySQL Database size:

# mysql -u root -p
Enter the Password:

SELECT table_schema "Data Base Name",
sum( data_length + index_length ) / 1024 /
1024 "Data Base Size in MB",
sum( data_free )/ 1024 / 1024 "Free Space in MB"
FROM information_schema.TABLES
GROUP BY table_schema ;

ORSELECT 'Total DB Size' SizeInMB,
sum( data_length + index_length ) / 1024 /
1024 "Data Base Size in MB"
FROM information_schema.TABLES
where table_schema not in ('mysql','information_schema');


9. To view individual Database Size in MySQL :

# mysql -u root -p
Enter the Password:

SELECT table_schema                                        "DB Name",
   Round(Sum(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB"
FROM   information_schema.tables
GROUP  BY table_schema;

ORSELECT table_schema,
sum( data_length + index_length ) / 1024 /
1024 "Data Base Size in MB"
FROM information_schema.TABLES
where table_schema not in ('mysql','information_schema','performance_schema')
group by table_schema
order by 2 desc;


10. MySQL Cluster Status :

# mysql -u root -p
Enter the Password:

mysql> show status like 'wsrep%';

11. To Check MySQl Cluster is Synced or Not :# /usr/bin/clustercheck root root_password

Thanks For Visiting on My Blog, For More Tutorials Keep Visiting My Blog