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:
To view all live connections details:
mysql> show processlist;
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
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.
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.
- Stop mysql daemon.
- 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
- Now connect mysql without password
$ mysql --user=root mysql
- Then run following commands in mysql.
At new-password enter your new password.