Important Commandline Tools to Monitor MySQL Performance in Linux

Tools to monitor MySQL performance and troubleshoot a server

Mytop

Mytop is open source and free console-based (non-gui) monitoring tool for MySQL database Mytop runs in a terminal and displays statistics about threads, queries, slow queries, uptime, load, etc. in tabular format, which is much similar to the Linux top program.


Mtop

mtop (MySQL top) is a ohter open source, command line based real time MYSQL Server monitoring tool,

Innotop

Innotop is  advanced command line based monitoring program to monitor local and remote MySQL servers running InnoDB engine.

mysqladmin

mysqladmin is a inbuilt command line MySQL it helps such as monitoring processes, checking server configuration, reloading privileges, current status, setting root password, changing root password, create/drop databases, and much more.

Back Up and Restore a MySQL Database

You can backup your MySQL data by using the mysqldump command. This command connects to the MySQL server and creates an SQL dump file

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

[uname] Your database username
[pass] The password for your database
[dbname] The name of your database
[backupfile.dump] The filename for your database backup
[--opt] The mysqldump option

For example, to backup a database name 'mydb'
$ mysqldump -u root -p Tutorials > mydb_backup.dump

With mysqldump command you can specify  tables of your database you want to backup

mysqldump -u root -p mydb mytable  > mytable_backup.dump

If you want to back up all the databases in the server at one time you should use the --all-databases option.

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

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.


Back up your MySQL Database with Compress

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

Restoring your MySQL Database

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.dump]

how you can restore your mydb.dump file to the mydb database.

$ mysql -u root -p mydb < mydb.dump

Grant on mysql 5.7

 Administrative privileges enable users to manage operation of the MySQL server.Privileges for database objects such as tables, indexes, views, and stored routines can be granted for specific objects within a database,

 GRANT ALL ON *.* TO 'user'@'192.168.1.101' identified WITH mysql_native_password AS '*PRF7ACF1D1BAC80SF3D6717F07818CA98F320DLB'; 

mysql table is marked as crashed repair failed

mysql> repair table my_table;
+------------------------------+--------+----------+--------------------------------------------------------------------------+
| Table                        | Op     | Msg_type | Msg_text                                                                 |
+------------------------------+--------+----------+--------------------------------------------------------------------------+
| mydb.my_table                | repair | Error    | Table storage engine for 'my_table' doesn't have this option |
| mydb.my_table                | repair | error    | Corrupt                                                                  |
+------------------------------+--------+----------+--------------------------------------------------------------------------+
2 rows in set (0.00 sec)

 Go to your data directory and try running
#myisamchk -r my_table
 You should stop MySQL process first. If that doesn't work, you can try with
#myisamchk -r -v -f my_table

Solved (without restart) Mysql slow query log is on but not logging anything

1.First cross check /etc/my.cnf to make it permanent

slow_query_log = 1
slow_query_log_file = /var/log/mysql_slow_query.log

2.Permission of

mysql mysql  763482 Oct  6 11:43 /var/log/mysql_slow_query.log

3.Slow query logging without restart mysql

login to  mysql as root
mysql> set global slow_query_log = 1;
mysql> set global slow_query_log_file = '/var/log/mysql_slow_query.log';
mysql> flush privileges;

Cheers!!!!!!!

Solved Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work

Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work

1. check and correct server-id under /etc/my.cnf
2. remove auto.cnf file under data directory then restart mysql 

Dumping MySQL Stored Procedures, Functions and Triggers

Mysqldump will backup by default all the triggers but NOT the stored procedures/functions. There are 2 mysqldump parameters that control this behavior:

Assume we want to backup only the stored procedures and triggers
 the mysql tables and data (this can be useful to import these in another db/server that has already the data but not the stored procedures and/or triggers),
 then we should run something like:

mysqldump -u root -p'password' --routines  --triggers --no-create-info --no-data  --no-create-db --skip-opt db_name > dbfunction.sql

Mysql slow query log not working

Add this in my.cnf and then restart MySQL server:

general_log = 1
log=/var/log/mysql_slowquery.log

You can check whether the general_log is 'ON' or 'OFF' first using query:

mysql>show variables like '%log%';

You can enable it by below commoand without restart

SET GLOBAL slow_query_log = 'ON';

Mysql change table engine MyISAM to InnoDB

 The easiest way is

ALTER TABLE table_name ENGINE = InnoDB;

InnoDB uses row level locks

Command to optimize all MySQL tables one by one automatically

 use mysqlcheck with --optimize and --databases combination. mysqlcheck is a command-line interface for administrators


 mysqlcheck -uroot -p --optimize --databases myDatabase

Optimizing a Table

It's simple and straightforward to optimize a table in MySQL

Optimize table TABLE_NAME