How to find the Uptime of MySQL Instance

How can I find, since when my MySQL Instance is running

login to your MySQL server and execute

mysql> show status like 'Uptime';
+---------------+---------+
| Variable_name | Value   |
+---------------+---------+
| Uptime        | 2507101 |
+---------------+---------+
1 row in set (0.00 sec)

Note:- The Uptime value displayed here is in Seconds. My MySQL instance is running since
2507101 seconds

OR

mysql> \s;
--------------
mysql  Ver 14.14 Distrib 5.1.73, for redhat-linux-gnu (x86_64) using readline 5.1
Connection id:          289795
Current database:
Current user:           oracle@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         5.1.73-log Source distribution
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    utf8
Db     characterset:    utf8
Client characterset:    latin1
Conn.  characterset:    latin1
UNIX socket:            /var/lib/mysql/mysql.sock
Uptime:                 29 days 21 min 3 sec
Threads: 6  Questions: 14313149  Slow queries: 9  Opens: 77  Flush tables: 1  Open tables: 57  Queries per second avg: 5.709
--------------
ERROR:
No query specified

OR

mysql> status
--------------
mysql  Ver 14.14 Distrib 5.1.73, for redhat-linux-gnu (x86_64) using readline 5.1
Connection id:          289820
Current database:
Current user:           oracle@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         5.1.73-log Source distribution
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    utf8
Db     characterset:    utf8
Client characterset:    latin1
Conn.  characterset:    latin1
UNIX socket:            /var/lib/mysql/mysql.sock
Uptime:                 29 days 28 min 45 sec
Threads: 6  Questions: 14314196  Slow queries: 9  Opens: 77  Flush tables: 1  Open tables: 57  Queries per second avg: 5.708
--------------
mysql>

mysqldump: Error: Binlogging on server not active

How to Fix Error: Binlogging on server not active

# mysqldump --all-databases --user=root --password --master-data > Fullbackup_26Jul2017.sql
Enter password:

mysqldump: Error: Binlogging on server not active.

While trying to backup the MySQL Databases I am experiencing this error. The error is pretty much self explaining and the simply the reason is that the logging is not enabled and I am trying an online backup

Resolution 1: Take Cold backup Instead of Hotbackup

How to Perform MySQL Database Backup

Cold Backups

Cold backups are a type of physical backup as you copy the database files while the database is offline.

Cold Backup

The basic process of a cold backup involves stopping MySQL, copying the files, the restarting MySQL. You can use whichever method you want to copy the files (cp, scp, tar, zip etc.).

# service mysql stopShutting down MySQL.. SUCCESS!

#cd /var/lib/mysql

#cp -r * /u01/stage/backup

# service mysql startStarting MySQL... SUCCESS!
[root@node1 mysql]#



Resolution 2: Enable Binlogging and then trigger the Logical Backup Again

How to Enable Binary Logging in MySQL

To enable the binary logs, edit the "/etc/my.cnf"  uncomment the line log_bin and restart the MySQL

#service mysql stop
Shutting down MySQL.. SUCCESS!
# service mysql start
Starting MySQL. SUCCESS!

mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set (0.00 sec)

mysqldump --all-databases --user=root --password --master-data > Fullbackup_26Jul2017.sql
Enter password:

mysqldump Error 2013: Lost connection to MySQL server

mysqldump Error 2013: Lost connection to MySQL server during query when dumping table 'string' at row

Solution: Increase the value of


net_read_timeout = 120
net_write_timeout = 900

and

max_allowed_packet=256 or even 1GB and try mysqldump again.

Default value for me was

net_read_timeout            60
net_write_timeout           60
max_allowed_packet      4194304

Changing the value as mentioned below solved my Issue.

net_read_timeout = 120
net_write_timeout = 900
max_allowed_packet=256  MB

You can Change the value of These variables either in my.cnf file which requires restart of the MYSQL sever

or

Just session or Global Level using set command

set global net_write_timeout=900;
Query OK, 0 rows affected (0.00 sec)

set global net_read_timeout=120;
Query OK, 0 rows affected (0.00 sec)

set global max_allowed_packet=268435456;

Query OK, 0 rows affected (0.00 sec)

How to Perform MySQL Database Point-in-Time Recovery

How to Perform MySQL Database Point-in-Time Recovery

Restore the Database from last full backup ( the post discuss restoring just a single MySQL database only) you can adjust the commands according to your requirement.

Database anand was accidentally dropped and we have full dump and binary log backup which I am using to restore the database back


Database Anand is missing

show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| menagerie          |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

Restore single database (anand) from last fullbackup

mysql -u root -p < C:\mysql\backup\MySQL_Sunday_Full_Backup.sql

Enter password: *******

after successfull restore database anand is back again.

show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| anand              |
| menagerie          |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.00 sec)

use anand;

Database changed

show tables;
+-----------------+
| Tables_in_anand |
+-----------------+
| help            |
| t1              |
+-----------------+
2 rows in set (0.00 sec)


Ohh what's that I can see there are some tables still missing. Come on it is expected as the full backup I used to restore is a bit older and does not contain the changes made after fullbackup.

To bring forward the database and recover the missing data we must apply the incremental changes.
To identify which binary logs we Need for the same ,  just open the fullbackup dump and you will find line something like this.
--
-- Position to start replication or point-in-time recovery from
--
-- CHANGE MASTER TO MASTER_LOG_FILE='bin_log.000019', MASTER_LOG_POS=154;


This means we need binary log from bin_log.000019 till now (till point of time of recovery)

Applying Incremental Backup for database anand

mysqlbinlog bin_log.000019 bin_log.000020 bin_log.000021  bin_log.000022 bin_log.000023 bin_log.000024 bin_log.000025 |mysql anand -u root -p

Enter password: *******

Note:- did you notice I am passing all the binarylogs required for this recovery in one mysqlbinlog command. This is very importent Piece of Information. Please do not execute something like this. Ist dangerous.


shell> mysqlbinlog bin_log.000019 | mysql -u root -p

shell> mysqlbinlog bin_log.0000120| mysql -u root -p
.......................................................................................
.......................................................................................
.......................................................................................

For more Infrmation check out
https://dev.mysql.com/doc/refman/5.7/en/point-in-time-recovery.html

After successfully applying the incremental backup I can see all the tables and data in the table is back again.

I am happy.

use anand;
Database changed

show tables;
+-----------------+
| Tables_in_anand |
+-----------------+
| help            |
| t1              |
| t2              |
+-----------------+
3 rows in set (0.00 sec)

Good Luck ;

Backup MySQL database using mysqldump and Binary Logs.

How to Perform fullbackup /Incremental backup

MySQL Databases

This post discuss only MySQL Database Backup Using mysqldump (Logical backup) InnoDB Storage Engine

Its pretty Easy. Just execute the below command. Adjust the user as per your setup and you are done

mysqldump -u root -p --single-transaction --flush-logs --master-data=2 --all-databases > C:\mysql\backup\MySQL_Sunday_Full_Backup.sql

Enter password: *******


If you open the dump you will see something like this which indicates until what point your dump contains the data and if you need to perform point in time recovery in future from what point onward you need binary logs

-- Position to start replication or point-in-time recovery from
--
-- CHANGE MASTER TO MASTER_LOG_FILE='bin_log.000019', MASTER_LOG_POS=154;

Performing Incremental backup

Performing Incremental backup is even easier. Just flush the logs and secure them on a safe drive which you can use later as and when required.

To flush logs you can use

flush logs;
mysql> flush logs;
Query OK, 0 rows affected (0.07 sec)

 or
mysqladmin flush-logs
mysqladmin -u root -p flush-logs
Enter password: *******

copy the binarylogs to secure location using cp, copy or whatever applicable for your OS.


Additional Notes

How to identify the genarated binary log sequences
mysql> show master logs;
+----------------+-----------+
| Log_name       | File_size |
+----------------+-----------+
| bin_log.000001 |       177 |
| bin_log.000002 |       583 |
| bin_log.000003 |      1941 |
| bin_log.000004 |       154 |
| bin_log.000005 |       154 |
| bin_log.000006 |      1790 |
| bin_log.000007 |       177 |
| bin_log.000008 |      4407 |
| bin_log.000009 |      5574 |
| bin_log.000010 |       329 |
| bin_log.000011 |       177 |
| bin_log.000012 |       430 |
| bin_log.000013 |      2007 |
| bin_log.000014 |       177 |
| bin_log.000015 |      2284 |
| bin_log.000016 |       177 |
| bin_log.000017 |       199 |
| bin_log.000018 |       199 |
| bin_log.000019 |       199 |
| bin_log.000020 |       199 |
| bin_log.000021 |       199 |
| bin_log.000022 |       199 |
| bin_log.000023 |      1639 |
| bin_log.000024 |       199 |
| bin_log.000025 |       154 |
+----------------+-----------+
25 rows in set (0.01 sec)

How to identify the binary Log location

mysql> show variables like '%log_bin%';
+---------------------------------+------------------------+
| Variable_name                   | Value                  |
+---------------------------------+------------------------+
| log_bin                         | ON                     |
| log_bin_basename                | C:\mysql\bin_log       |
| log_bin_index                   | C:\mysql\bin_log.index |
|
+---------------------------------+------------------------+
6 rows in set, 1 warning (0.00 sec)


See you next time with Next Post till then

Enjoy learning & Have Fun.....

Granting and Revoking Privileges Mysql Server

Privilege Management in MySQL  Server

Identify what privileges do you want to grant and on what database and follow below steps to grant

Here I am granting privileges for database anand to test

grant create on anand.* to test;

Query OK, 0 rows affected (0.00 sec)

create table help (id int);

Query OK, 0 rows affected (0.07 sec)

select * from help;

ERROR 1142 (42000): SELECT command denied to user 'test'@'localhost' for table 'help'

grant select on anand.help to test;

Query OK, 0 rows affected (0.00 sec)

select * from help;

Empty set (0.00 sec)

insert into help values (10);

ERROR 1142 (42000): INSERT command denied to user 'test'@'localhost' for table 'help'

grant insert on anand.help to test;

Query OK, 0 rows affected (0.00 sec)

insert into help values (10);

Query OK, 1 row affected (0.00 sec)

select * from help;
+------+
| id   |
+------+
|   10 |
+------+
1 row in set (0.00 sec)

If you want to revoke the privilege from an user you can do so as mentioned below

REVOKE select on anand.help from test;

Query OK, 0 rows affected (0.02 sec)

select * from help;

ERROR 1142 (42000): SELECT command denied to user 'test'@'localhost' for table 'help'

Althoug you should avoid any such grant, however you can grant the privleges with grant option which let the grantee authorized to futher delegate the granted privileges


grant all on anand.* to test with grant Option;

Query OK, 0 rows affected (0.00 sec)

How to secure Login credentials in MySQL using mysql_config_editor (.mylogin.cnf.)

Use the mysql_config_editor to Secure the connection Information

mysql_config_editor set --login-path=MySQL57 --host=localhost --user=root --port=3306 --Password

Enter password: *******

Confirm that the login-path data was correctly added to .mylogin.cnf

mysql_config_editor print --login-path=MySQL57
or
mysql_config_editor print --all

[MySQL57]
user = root
password = *****
host = localhost
port = 3306

Connect to MySQL server using .mylogin.cnf.

mysql --login-path=MySQL57

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.18-log MySQL Community Server (GPL)
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.


The execution of a utility specifying the login-path section.

mysqlserverinfo --server=MySQL57 --format=vertical

# Source on localhost: ... connected.
*************************       1. row *************************
                   server: localhost:3306
              config_file: c:\my.ini
               binary_log: bin_log.000015
           binary_log_pos: 154
                relay_log:
            relay_log_pos:
                  version: 5.7.18-log
                  datadir: C:\ProgramData\MySQL\MySQL Server 5.7\Data\
                  basedir: C:\Program Files (x86)\MySQL\MySQL Server 5.7\
               plugin_dir: C:\Program Files (x86)\MySQL\MySQL Server 5.7\lib\plugin\
              general_log: ON
         general_log_file: C:/mysql/general_log.log
    general_log_file_size: 3608472 bytes
                log_error: C:\mysql\mysql_log.log
      log_error_file_size: 122782 bytes
           slow_query_log: ON
      slow_query_log_file: C:/mysql/sql_log.log
 slow_query_log_file_size: 3105 bytes
1 row.
#...done.


Where to find .mylogin.cnf. file ?


The file location is the %APPDATA%\MySQL directory on Windows and the current user's home directory on non-Windows Systems

ERROR 1040 (HY000): Too many connections: MySQL

ERROR 1040 (HY000): Too many Connections

Login to your MySQL Server and check following

show global status like '%max_used%';

+---------------------------+---------------------+
| Variable_name             | Value               |
+---------------------------+---------------------+
| Max_used_connections      | 3                   |
| Max_used_connections_time | 2017-06-08 10:39:06 |
+---------------------------+---------------------+
2 rows in set (0.00 sec)


show variables like '%max%connection%';

+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| max_connections      | 3     |
| max_user_connections | 2     |
+----------------------+-------+
2 rows in set, 1 warning (0.00 sec)

Now was you can see here total allowed Connection (max_connections) are 3 and all the Connections are already used. Trying to establish session (conection) beyond this value is causing ERROR 1040 (HY000): Too many Connections error.


Solutions

Identify ideal Connection and kill if possible to free up the Connection. In my case both the session (ID 11 and 12 ) are sleeping and I can kill any one of them to free up 1 Connection.

Please be Aware of the risk if you are trying to kill the session in Prod Environment


show processlist;

+----+------+-----------------+-----------+---------+------+----------+------------------+
| Id | User | Host            | db        | Command | Time | State    | Info             |
+----+------+-----------------+-----------+---------+------+----------+------------------+
| 11 | test | localhost:55377 | menagerie | Sleep   |  887 |          | NULL             |
| 12 | test | localhost:55488 | menagerie | Sleep   |  643 |          | NULL             |
| 15 | root | localhost:55880 | NULL      | Query   |    0 | starting | show processlist |
+----+------+-----------------+-----------+---------+------+----------+------------------+

kill 11;

Query OK, 0 rows affected (0.00 sec)

show processlist;

+----+------+-----------------+-----------+---------+------+----------+------------------+
| Id | User | Host            | db        | Command | Time | State    | Info             |
+----+------+-----------------+-----------+---------+------+----------+------------------+
| 12 | test | localhost:55488 | menagerie | Sleep   |  913 |          | NULL             |
| 16 | root | localhost:56735 | NULL      | Query   |    0 | starting | show processlist |
+----+------+-----------------+-----------+---------+------+----------+------------------+
2 rows in set (0.00 sec)


OR

Increase the value of max_connections variable

set global max_connections=10;

Query OK, 0 rows affected (0.00 sec)

show variables like '%max%connection%';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| max_connections      | 10    |
| max_user_connections | 2     |
+----------------------+-------+
2 rows in set, 1 warning (0.00 sec)


Note:- Please beaware of that the value changed using set command here will not be persistent acroos the restart of your MySQL Server and therefore to make the Setting persistent across the restart please modify my.cnf or my.ini accordingly whatever applicable


If you like my work then please like the post and leave your comment in the Comment section

How to Find the Status of MySQL Server

Find the status of MySQL server

There are several possible ways to check the staus of MySQL server.

You need to execute the below commands from bin directory of MySQL installation.

On Linux / Windows

mysqladmin -u root -p Status

Enter password: *******
Uptime: 154500  Threads: 3  Questions: 205  Slow queries: 0  Opens: 131  Flush tables: 1  Open tables: 118  Queries per second avg: 0.001


mysqladmin -u root -p ping

Enter password: *******
mysqld is alive


On Linux only

service mysqld Status

On Windows only

Check the MySQL service Status.

Who is connected to your MyQL Server

How to find who is connected to your MY SQL Server?

it is very essy to find out who is connected to your MySQL server
just query processlist table of your Information_Schema


mysql> select * from PROCESSLIST;
+----+------+-----------------+--------------------+---------+------+-----------+---------------------------+
| ID | USER | HOST | DB | COMMAND | TIME | STATE | INFO |
+----+------+-----------------+--------------------+---------+------+-----------+---------------------------+
| 6 | root | localhost:60766 | information_schema | Query | 0 | executing | select * from PROCESSLIST |
+----+------+-----------------+--------------------+---------+------+-----------+---------------------------+
1 row in set (0.00 sec)

or

your favorite Show command



mysql> show processlist;
+----+------+-----------------+--------------------+---------+------+----------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------------+--------------------+---------+------+----------+------------------+
| 6 | root | localhost:60766 | information_schema | Query | 0 | starting | show processlist |
+----+------+-----------------+--------------------+---------+------+----------+------------------+
1 row in set (0.00 sec)


please like the post and leave your comment if you find the post usable.