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

Speedly MySQL InnoDB Shutdown

Depending on the size of the databases you have in mysql innodb the time it takes mysql to restart can be very slow.Some tricks that can speed this up


mysql> set global innodb_max_dirty_pages_pct = 0;

You can check the number of dirty pages with the command

mysqladmin ext -i10 | grep dirty

SELECT without Table

You can also issue SELECT without a table. For example, you can SELECT an expression or evaluate a built-in function.

mysql> select 1+1;

+-----+

| 1+1 |

+-----+

|   2 |

+-----+

1 row in set (0.00 sec)

mysql> select now();

+---------------------+

| now()               |

+---------------------+

| 2016-05-03 12:40:41 |

+---------------------+

1 row in set (0.00 sec)

mysql> select now(), 1+2;

+---------------------+-----+

| now()               | 1+2 |

+---------------------+-----+

| 2016-05-03 12:40:55 |   3 |

+---------------------+-----+

1 row in set (0.00 sec)

Recover a MySQL root password

Simple and smart reset mysql root password

# /etc/init.d/mysql stop
# mysqld_safe --skip-grant-tables &
# mysql -u root
mysql> use mysql;
mysql> update user set password=PASSWORD("newrootpassword") where User='root';
mysql> flush privileges;
mysql> quit
# /etc/init.d/mysql stop
# /etc/init.d/mysql start

Save the Select Output to a File

Using SELECT INTO, we can save the output of a select command into a file.Instead of displaying the output on the screen

mysql> SELECT * INTO OUTFILE '/tmp/employee.txt' FROM employee;

We can also store the output into a comma delimited file by specifying the “FIELDS TERMINATED BY”

mysql> SELECT * INTO OUTFILE '/tmp/employee1.txt'   FIELDS TERMINATED BY ',' FROM employee;

Mysql query result into HTML or XML

mysql can do that for you. Use mysql -H or mysql-X.

mysql generates an HTML table from each query result set if you use the -H (or --html) option mysql creates an XML document from the result of a statement if you use the -X (or --xml) option:

mysql  db_name -H -e "select * from table_name";
mysql  db_name -X -e "select * from table_name";

What is the default root pasword for MySQL 5.7

After you installed MySQL-community-server 5.7 from fresh on linux

grep 'temporary password' /var/log/mysqld.log

Upgrade From MySQL 5.1 to 5.7 checklilst

Find mysql upgrade checklist

1.Dump all databases/schemas from the existing mysql server by using mysqldump command
2.intall/Initialize a new MySQL 5.7 server instance
3.Load the dump file into the new MySQL 5.7 server
4.Run mysql_upgrade (all the system tables upgraded)
5.Keep in mind for mysql users and grants

ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

Mysql 5.7 password verification plugin (and impacts to PASSWORD() function)

mysql>  SHOW VARIABLES LIKE 'validate_password%';
+--------------------------------------+--------+
| Variable_name                        | Value  |
+--------------------------------------+--------+
| validate_password_dictionary_file    |        |
| validate_password_length             | 8      |
| validate_password_mixed_case_count   | 1      |
| validate_password_number_count       | 1      |
| validate_password_policy             | MEDIUM |
| validate_password_special_char_count | 1      |
+--------------------------------------+--------+
6 rows in set, 1 warning (0.00 sec)

Workaround is setting /etc/my.conf using validate-password = off
and reload mysql server

view a list of MySQL users and their privileges

You are running a multi-user MySQL database, some useful commands that show a list of all existing MySQL users
and their privileges. To find out all MySQL users and the permissions granted to each user, log in to your MySQL server,
and run the below MySQL commands.

mysql>select user,host from mysql.user;

+-----------+-------------+
| user      | host        |
+-----------+-------------+
| dbb        | %           |
| root      | 127.0.0.1   |
| dbb        | 192.168.1.% |
| root      | 192.168.1.% |
| dbb        | localhost   |
| mysql.sys | localhost   |
| root      | localhost   |
+-----------+-------------+
7 rows in set (0.00 sec)



mysql>  show grants for 'root'@'192.168.1.%';
+-----------------------------------------------------+
| Grants for root@192.168.1.%                         |
+-----------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.1.%' |
+-----------------------------------------------------+
1 row in set (0.00 sec)

MySQL user DB does not have password columns - Installing MySQL5.7

In MySQL 5.7, the password field in mysql.user table field was removed, now the field name is 'authentication_string'.
First use the database
mysql>use mysql;
mysql>show tables;
You will find the user table, now let's see its fields:
mysql> desc user;
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field                  | Type                              | Null | Key | Default               | Extra |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host                   | char(60)                          | NO   | PRI |                       |       |
| User                   | char(32)                          | NO   | PRI |                       |       |
| Select_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Insert_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Update_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Delete_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Create_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Drop_priv              | enum('N','Y')                     | NO   |     | N                     |       |
| Reload_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Shutdown_priv          | enum('N','Y')                     | NO   |     | N                     |       |
| Process_priv           | enum('N','Y')                     | NO   |     | N                     |       |
| File_priv              | enum('N','Y')                     | NO   |     | N                     |       |
| Grant_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| References_priv        | enum('N','Y')                     | NO   |     | N                     |       |
| Index_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| Alter_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| Show_db_priv           | enum('N','Y')                     | NO   |     | N                     |       |
| Super_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| Create_tmp_table_priv  | enum('N','Y')                     | NO   |     | N                     |       |
| Lock_tables_priv       | enum('N','Y')                     | NO   |     | N                     |       |
| Execute_priv           | enum('N','Y')                     | NO   |     | N                     |       |
| Repl_slave_priv        | enum('N','Y')                     | NO   |     | N                     |       |
| Repl_client_priv       | enum('N','Y')                     | NO   |     | N                     |       |
| Create_view_priv       | enum('N','Y')                     | NO   |     | N                     |       |
| Show_view_priv         | enum('N','Y')                     | NO   |     | N                     |       |
| Create_routine_priv    | enum('N','Y')                     | NO   |     | N                     |       |
| Alter_routine_priv     | enum('N','Y')                     | NO   |     | N                     |       |
| Create_user_priv       | enum('N','Y')                     | NO   |     | N                     |       |
| Event_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| Trigger_priv           | enum('N','Y')                     | NO   |     | N                     |       |
| Create_tablespace_priv | enum('N','Y')                     | NO   |     | N                     |       |
| ssl_type               | enum('','ANY','X509','SPECIFIED') | NO   |     |                       |       |
| ssl_cipher             | blob                              | NO   |     | NULL                  |       |
| x509_issuer            | blob                              | NO   |     | NULL                  |       |
| x509_subject           | blob                              | NO   |     | NULL                  |       |
| max_questions          | int(11) unsigned                  | NO   |     | 0                     |       |
| max_updates            | int(11) unsigned                  | NO   |     | 0                     |       |
| max_connections        | int(11) unsigned                  | NO   |     | 0                     |       |
| max_user_connections   | int(11) unsigned                  | NO   |     | 0                     |       |
| plugin                 | char(64)                          | NO   |     | mysql_native_password |       |
| authentication_string  | text                              | YES  |     | NULL                  |       |
| password_expired       | enum('N','Y')                     | NO   |     | N                     |       |
| password_last_changed  | timestamp                         | YES  |     | NULL                  |       |
| password_lifetime      | smallint(5) unsigned              | YES  |     | NULL                  |       |
| account_locked         | enum('N','Y')                     | NO   |     | N                     |       |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
45 rows in set (0.00 sec)

update user set authentication_string=PASSWORD('new_pass') where User='root';

Fix the error “Mysql Server has gone away (error 2006)”?

Server timed out and closed the connection. To fix, check that “wait_timeout” mysql variable in your my.cnf configuration file is large enough

 Server dropped an incorrect or too large packet. If mysqld gets a packet that is too large or incorrect, assumes that something has gone wrong and closes the connection.
 To fix, increase the maximal packet size limit “max_allowed_packet” in my.cnf file,
e.g. set max_allowed_packet = 128M, then  /etc/init.d/mysql restart.

Using SELECT statement find the version of the server you are running and print the name of the current database

 The below MySQL command will show server version and currently selected Database.

 mysql> SELECT VERSION(), DATABASE();

Repair all tables in one-shot

Rather than typing the query repair table ''tablename''; for all the tables one by one.

# mysqlcheck -u root --password=thepass --auto-repair --check --all-databases

Check All Tables in a Database

 To check all the tables in a particular database, don’t specify the table name. Just specify the database name.
 The following example checks all the tables in the db_name database.

#mysqlcheck -c db_name  -u root -p
Enter password:
db_name.JBPM_ACTION                               OK
db_name.JBPM_BYTEARRAY                            OK
db_name.JBPM_BYTEBLOCK                            OK
db_name.JBPM_COMMENT                              OK
db_name.JBPM_DECISIONCONDITIONS                   OK
db_name.JBPM_DELEGATION                           OK
db_name.JBPM_EVENT                                OK


Check a Specific Table in a Database

If your application gives an error message saying that a specific table is corrupted, execute the mysqlcheck command to check that one table.

The following example checks employee table in db_name database.

# mysqlcheck -c db_name table_name -u root -p
Enter password:
db_name.table_name    OK

Top 10 Mysql Slow Query

MySQL has a nice feature: MySQL slow query log, which allows you to log all queries that exceed a predefined about of time to execute.
Go or define mysql slow query file path and run

mysqldumpslow -s c -t 10 

The MySQL Query Cache

Query caching is one of the important features in MySQL and a vital part of query optimization

mysql>show VARIABLES where variable_name regexp 'query_cache';
+------------------------------+---------+
| Variable_name                | Value   |
+------------------------------+---------+
| have_query_cache             | YES     |
| query_cache_limit            | 1048576 |
| query_cache_min_res_unit     | 4096    |
| query_cache_size             | 0       |
| query_cache_type             | ON      |
| query_cache_wlock_invalidate | OFF     |
+------------------------------+---------+

6 rows in set (0.00 sec)

mysql>SHOW GLOBAL STATUS WHERE variable_name REGEXP 'Qcache';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Qcache_free_blocks      | 0     |
| Qcache_free_memory      | 0     |
| Qcache_hits             | 0     |
| Qcache_inserts          | 0     |
| Qcache_lowmem_prunes    | 0     |
| Qcache_not_cached       | 0     |
| Qcache_queries_in_cache | 0     |
| Qcache_total_blocks     | 0     |
+-------------------------+-------+
8 rows in set (0.00 sec)

Quickly rebuild indexes

mysql> REPAIR TABLE mytable QUICK;
+—————-+————+—————+—————+
| Table     | Op     | Msg_type | Msg_text |
+—————-+————+—————+—————+
| mytable   | repair | status   | OK       |
+—————-+————+—————+—————+
1 row in set (0.05 sec)

View query results in XML format

You can have MySQL return query results to you in well-formed XML
shell>mysql --xml

mysql> SELECT * FROM test.stories;
<?xml version="1.0"?>

<resultset statement="SELECT * FROM test.stories">
  <row>
        <id>1</id>
        <headline>This is a test</headline>
        <tstamp>2005-07-28 00:14:57</tstamp>
  </row>

  <row>
        <id>2</id>
        <headline>This is the second test</headline>
        <tstamp>2005-07-28 00:15:11</tstamp>
  </row>
</resultset>
2 rows in set (0.11 sec)

Mysql user management

# Create user
CREATE USER user IDENTIFIED BY 'password';

# Create and user (if doesn´t exist) and grant permissions
GRANT ALL ON *.* TO 'user'@'localhost' IDENTIFIED BY 'password';

# Update
UPDATE [table] SET [table_field]=[new_value] WHERE [id]=[id];

# Change user password
SET PASSWORD FOR 'user'@'host' = PASSWORD('newpass');

MySQL replication - slave is lagging behind master

The Seconds_Behind_Master is just like viewing the past via time travel.

Just think like this:

The Sun is 93,000,000 miles away from the Earth
The speed of light is 186,000 miles/sec
Simple division shows that it takes roughly 500 sec (8 min 20 sec) for the Sun's light to reach Earth
When you look at the Sun, you actually don't see the Sun. You see where it was 8 min 20 sec ago.
In same manner, it seems that the Master is processing a lot of queries at the same time.

You look back at the Slave,
run SHOW SLAVE STATUS\G and it says 200 for Seconds_Behind_Master.
How is that number calculated?
Slave's Clock Time (UNIX_TIMESTAMP(NOW()) - TIMESTAMP of the Query
when it was completed and recorded in the Master's Binary Log.

Enable InnoDB Engine MySQL Database server

ERROR 1286 (42000) at line 1: Unknown table engine 'innodb'

Stop MySQL service:
# /etc/init.d/mysql stop

Open the file /etc/my.cnf (
Find the skip-innodb parameter and either remove it or comment the line by adding # at the beginning of the line:

#skip-innodb
Insert the following parameters into the [mysqld] and [mysqld_safe] sections in /etc/my.cnf:
innodb_buffer_pool_size=16M
innodb_additional_mem_pool_size=500K
innodb_log_buffer_size=500K
innodb_thread_concurrency=2

Save the file and start MySQL:
# /etc/init.d/mysql start

In case still innoDB not showing
# /etc/init.d/mysql stop
move ibdata1 and ib_logfile from mysql data directory
# /etc/init.d/mysql start

Solved MySQL replication after slaves's relay log was corrupted

mysql>show slave status\G;

Note
Relay_Master_Log_File: mysql-bin.002045
Exec_Master_Log_Pos: 1038350378

# stop slave
mysql> stop slave;

# make slave forget its replication position in the master's binary log
mysql> reset slave;

# change slave to start reading from stopped position
mysql> change master to master_log_file='mysql-bin.002045', master_log_pos=1038350378;

# start slave
mysql> start slave;