Tag: Mysql

Node.js handling mysql disconnects.

Posted on by admin

You may lose the connection to a MySQL server due to network problems, the server timing you out, the server being restarted, or crashing. All of these events are considered fatal errors, and will have theĀ err.code = 'PROTOCOL_CONNECTION_LOST'. See theĀ Error HandlingĀ section for more information.

A good way to handle such unexpected disconnects is shown below:

 

var db_config = {
  host: 'localhost',
    user: 'root',
    password: '',
    database: 'example'
};

var connection;

function handleDisconnect() {
  connection = mysql.createConnection(db_config); // Recreate the connection, since
                                                  // the old one cannot be reused.

  connection.connect(function(err) {              // The server is either down
    if(err) {                                     // or restarting (takes a while sometimes).
      console.log('error when connecting to db:', err);
      setTimeout(handleDisconnect, 2000); // We introduce a delay before attempting to reconnect,
    }                                     // to avoid a hot loop, and to allow our node script to
  });                                     // process asynchronous requests in the meantime.
                                          // If you're also serving http, display a 503 error.
  connection.on('error', function(err) {
    console.log('db error', err);
    if(err.code === 'PROTOCOL_CONNECTION_LOST') { // Connection to the MySQL server is usually
      handleDisconnect();                         // lost due to either server restart, or a
    } else {                                      // connnection idle timeout (the wait_timeout
       throw err;                                 // server variable configures this)
    });
}

handleDisconnect();

As you can see in the example above, re-connecting a connection is done by establishing a new connection. Once terminated, an existing connection object cannot be re-connected by design.

With Pool, disconnected connections will be removed from the pool freeing up space for a new connection to be created on the next getConnection call.

Mysql list privileges for a user

Posted on by admin 
SHOW GRANTS;
SHOW GRANTS FOR CURRENT_USER;
SHOW GRANTS FOR CURRENT_USER();

e.g. SHOW GRANTS FOR root;

mysql> SHOW grants for root;
+———————————————————————————-+
| Grants for root@% |
+———————————————————————————-+
| GRANT ALL PRIVILEGES ON *.* TO ‘root’@’%’ IDENTIFIED BY PASSWORD ‘*81F5E21E35407D884A6CD4A731AEBFB6AF209E1B’ WITH GRANT OPTION |
+———————————————————————————-+
1 row in set (0.00 sec)

mysql>

 

nJoy šŸ˜‰

 

Reset Mysql password from bash prompt

Posted on by admin

You can recover MySQL database server password with following five easy steps.

Step # 1: Stop the MySQL server process.

Step # 2: Start the MySQL (mysqld) server/daemon process with the –skip-grant-tables option so that it will not prompt for password.

Step # 3: Connect to mysql server as the root user.

Step # 4: Setup new mysql root account password i.e. reset mysql password.

Step # 5: Exit and restart the MySQL server.

Here are commands you need to type for each step (login as the root user):

Step # 1 : Stop mysql service

# /etc/init.d/mysql stop
Output:

Stopping MySQL database server: mysqld.

Step # 2: Start to MySQL server w/o password:

# mysqld_safe --skip-grant-tables &
Output:

[1] 5988
Starting mysqld daemon with databases from /var/lib/mysql
mysqld_safe[6025]: started

Step # 3: Connect to mysql server using mysql client:

# mysql -u root
Output:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.15-Debian_1-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql>

Step # 4: Setup new MySQL root user password

mysql> use mysql;
mysql> update user set password=PASSWORD("NEW-ROOT-PASSWORD") where User='root';
mysql> flush privileges;
mysql> quit

Step # 5: Stop MySQL Server:

# /etc/init.d/mysql stop
Output:

Stopping MySQL database server: mysqld
STOPPING server from pid file /var/run/mysqld/mysqld.pid
mysqld_safe[6186]: ended
[1]+  Done                    mysqld_safe --skip-grant-tables

Step # 6: Start MySQL server and test it

# /etc/init.d/mysql start
# mysql -u root -p

 

Voila !

 

nJoy Ā šŸ˜‰

Monitoring a bottleneck with strace

Posted on by admin

If let’s say Mysql is slow at performing a task you can check what is the bottleneck using strace to attach to the process.

<span style="color: #00ff00;">$ ps -ef|grep -i mysql</span>

Identify the process id then

<span style="color: #00ff00;">$ strace -cp &lt;pid&gt;</span>

Leave it 10 seconds or a minute thenĀ ^C. That will tell you where the process is spending its time, e.g. it could just be waiting for the disk if you seenĀ readĀ andĀ writeĀ dominate.

Njoy šŸ™‚

MySQL: Grant **all** privileges on database

Posted on by admin

At mysql prompt as root user:

Ā GRANT ALL privileges ON *.* TO ‘user’@’machine.lan’ IDENTIFIED BY ‘password’ WITH GRANT OPTION;

Ā FLUSH PRIVILEGES;

 

That’s all

How to install MySQL on CentOS

Posted on by admin

Here are the steps of what you need to do in order to install and setup MySQL on a new server.

Weā€™ll prepare a fresh CentOS 6 system (64 bit) for use as a database server. All you need is access to an SSH client and your server root credentials.

 

Preparing the System

The first thing Iā€™d like to do is make sure I have all available updates. So once I log in I run

yum update

This can take some time so grab a coffee while you wait. Once yum has finished, letā€™s check if MySQL is already installed on your system:

mysql

If you get ā€œcommand not foundā€ then you know you need to install MySQL.

Installing MySQL and MySQL Server

MySQL consists of two parts: the client and the server. In order for our system to run the daemon which will process external requests we need both on our system. Hereā€™s how we get those:

yum install mysql mysql-server

This will take a minute or two. No need to restart your server, all you need to to is start MySQL with

service mysql start

and it will be available for use. Youā€™ll see the following message:

To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:

/usr/bin/mysqladmin -u root password 'new-password'
/usr/bin/mysqladmin -u root -h yourdomain.com password 'new-password'

Alternatively you can run:
/usr/bin/mysql_secure_installation

which will also give you the option of removing the test
databases and anonymous user created by default.Ā  This is
strongly recommended for production servers.

See the manual for more instructions.

You can start the MySQL daemon with:
cd /usr ; /usr/bin/mysqld_safe &

You can test the MySQL daemon with mysql-test-run.pl
cd /usr/mysql-test ; perl mysql-test-run.pl

Please report any problems with the /usr/bin/mysqlbug script!

Securing the installation

Looks like we need to do two things: set a root password and secure the installation. You can do these steps manually, but MySQL is rather nice in that it provides a script which you can use to secure your installation. Note that this path may be different on your system:

/usr/bin/mysql_secure_installation

The script will ask you the following questions:

  • current root password (in our case itā€™s not set so hit enter)
  • remove anonymous users (say yes)
  • disallow remote login (in our case we want remote login active so we say no here, but if youā€™re using MySQL on a system which will not need this then say yes here)
  • remove test database and access to it (say yes)
  • reload all privieleges (say yes)

Now you can access MySQL with the following command:

mysql -p

Starting MySQL at boot time

You will likely need to make sure MySQL is running when you reboot the server, itā€™s cumbersome to start it manually every time you do that. This will take care of it:

chkconfig --levels 235 mysqld on

Weā€™re done ā€“ MySQL is now running on your server and yours to populate.

Have funĀ ;-)

Allow remote SQL connection to Mysql from any host

Posted on by admin

Allowing the login of a user from any host in Mysql is simple:

mysql> select host, user from mysql.user;

+—————+——+
| host | user |
+—————+——+
| 127.0.0.1 | root |
| localhost | root |
| minimal01.lan | root |
+—————+——+
3 rows in set (0.00 sec)

mysql> update mysql.user set host=’%’ where host=’127.0.0.1′;

mysql> select host, user from mysql.user;

+—————+——+

| host | user |
+—————+——+
| % | root |
| localhost | root |
| minimal01.lan | root |
+—————+——+
3 rows in set (0.00 sec)

 

Voila`