Mysql get current connections

 

To get  current connections in mysql use any following command after logging into mysql from command line.

First login in into mysql:

$ mysql -u root -p'mySqlPassowrd'

Now enter any of following command:

To view only number of live connections:

mysql> SHOW STATUS WHERE `variable_name` = 'Threads_connected';

To view quick summery of all mysql details including live connections:

mysql> status;

To view all live connections details:

mysql> show processlist;
Advertisements

Install mysql server in Centos 5

 

To install mysql server in Centos 5 use following command:

$ yum install mysql mysql-server

After installing mysql server you need to set password for mysql root user with following command:

$ mysqladmin -u root password 'new-password'

Now try to access mysql with following command:

$ mysql -u root -p'new-password'

It will take you to myslq console.

To restart mysql server use following command:

$ service mysqld restart

To stop mysql server:

$ service mysqld stop

To start mysql server:

$ service mysqld start

To get status of mysql server:

$ service mysqld status

-Sany

MySql error 1 (hy000) can’t create/write to file (errcode 13)

 

Recently when I am exporting mysql command output to csv file I got following error.

ERROR 1 (HY000): Can’t create/write to file (Errcode: 13)

Here I am trying to write output to /home/someuser/somefile.csv I got this error.

After exploring a bit about the error I got to know is instead of writing to /home/someuser/somefile.csv we have to write to file /tmp/somefile.csv.

So I changed the output file to /tmp/somefile.csv in my sql command and its worked.

The reason for the error is permissions. While writing mysql command output into a directory we need have all permissions to that directory.

By default /tmp directory in Linux will have all permissions(777), so we need to write into /tmp directory.

-Sany

Linux forgot mysql password

 

If we are handling multiple mysql server we may forget mysql root password.

How to update new root password for mysql:

Just follow these 3 steps to update new password.

  1. Stop mysql daemon.
    • service mysqld stop

      (This command may differ with respect to your operating system).

  2. Now start mysql daemon by skipping the grant tables. Skip grant tables are which will store the passwords of mysql.
    • $ mysqld_safe --skip-grant-tables

  3. Now connect mysql without password
    • $ mysql --user=root mysql

    • Then run following commands in mysql.
      • update user set Password=PASSWORD('new-password') where user='root';
      • flush privileges;
      • exit;

At new-password enter your new password.

-Sany