Monday, October 8, 2012

Some tips on working with MySQL


I was working with MySQL for last last of couple of weeks and thought of sharing some important stuff that I found to be quite useful. I'm sure you can find all these by googling, but thought of having them in a single place. Hopefully this will save someone's time in the future.

  1. Start MySQL using .tar binary
  2. Start MySQL with enabling logs
  3. Change root password 
  4. Create user and allow admin privileges
  5. Allow remote IP to connect to your database
  6. Check table status 
  7. Repair a corrupted table
  8. Create a database dump and restore to a new database
  9. Pass a REGEX to select query

1. Start MySQL using .tar binary

I downloaded the mysql-5.5.27-osx10.6-x86_64.tar from official mysql site and extract it to a location. I go to extracted folder in the command line and start mysql in demon mode using following command.

sudo ./bin/mysqld_safe --basedir=/Users/chathuri/airavata/tools/mysql-5.5.27-osx10.6-x86_64 --datadir=/Users/chathuri/airavata/tools/mysql-5.5.27-osx10.6-x86_64/data/ --user=chathuri

Please note that, you need to specify admin user as --user and also make sure data directory is existing inside the extracted mysql directory.

Now you will see the following lines printed in your console and mysql server is being started in the demon mode.

121008 09:20:17 mysqld_safe Logging to '/Users/chathuri/airavata/tools/mysql-5.5.27-osx10.6-x86_64/data//140-182-209-114.dhcp-bl.indiana.edu.err'.
121008 09:20:17 mysqld_safe Starting mysqld daemon with databases from /Users/chathuri/airavata/tools/mysql-5.5.27-osx10.6-x86_64/data/

Now you should be able to connect to the mysql server that is being started in the deamon mode. To connect to mysql server run the mysql script in the bin folder.

140-182-209-114:mysql-5.5.27-osx10.6-x86_64 chathuri$ ./bin/mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.27 MySQL Community Server (GPL)

Copyright (c) 2000, 2011, 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.

mysql>

Now you will see that the mysql prompt is there and you can run basic mysql commands to create databases and etc.


2. Start MySQL with enabling logs

You can configure mysql to write all the logs in to a log file. Suppose I have a file called log.out inside the bin folder of mysql extracted location. To enable logs, you need to add --log in to the start command that we specified in the previous step. Now the server starting command will look like this.

sudo ./bin/mysqld_safe --log=/Users/chathuri/airavata/tools/mysql-5.5.27-osx10.6-x86_64/bin/log.out --basedir=/Users/chathuri/airavata/tools/mysql-5.5.27-osx10.6-x86_64 --datadir=/Users/chathuri/airavata/tools/mysql-5.5.27-osx10.6-x86_64/data/ --user=chathuri

Now if you do a tail to that file, and try to connect to mysql server as before, you will see all the logs are being written to that log.out file.

/Users/chathuri/airavata/tools/mysql-5.5.27-osx10.6-x86_64/bin/mysqld, Version: 5.5.27-log (MySQL Community Server (GPL)). started with:
Tcp port: 0  Unix socket: (null)
Time                 Id Command    Argument

/Users/chathuri/airavata/tools/mysql-5.5.27-osx10.6-x86_64/bin/mysqld, Version: 5.5.27-log (MySQL Community Server (GPL)). started with:
Tcp port: 0  Unix socket: (null)
Time                   Id Command    Argument
121008  9:43:32        1 Connect    chathuri@localhost as  on 
                       1 Query      select @@version_comment limit 1
121008  9:43:47        1 Query      show databases


3. Change root password

By default mysql does not have a root user. You need to add a root user and set the root password. To set up root password, you need to use mysqladmin script located in the bin folder in the command as below.

./bin/mysqladmin -u root password NEWPASSWORD

If you already have a root user and password, you can update password using mysqladmin as well. Suppose my current root password is "abc" and I want to change it to "root123", you can run the following command in the command prompt.

./bin/mysqladmin -u root  -p'abc' password root123

Note that there are no spaces after -p in the above command.


4. Create user and allow admin privileges

You will need to add user accounts. To do that, connect to mysql server as the root user and then you can add other user accounts using following commands.

mysql> CREATE USER 'jim'@'localhost' IDENTIFIED BY 'jim123';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL ON *.* TO 'jim'@'localhost' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql> exit;

Now try to connect to mysql server as user 'jim' and try to run some queries. 


5. Allow remote IP to connect to your database

You may need to allow some remote IPs to connect to your database. To allow that, connect to your database as an admin user and run the following commands.

Suppose I have a user called "airavata" with the password "airavata" and I want to allow 140.182.198.158 IP to connect to my database, run the below commands.

mysql> GRANT ALL PRIVILEGES ON *.* to airavata@'140.182.198.158' IDENTIFIED BY 'airavata';
mysql> FLUSH PRIVILEGES;


If you want to allow any IP to connect to your database, use the following commands. 

mysql> GRANT ALL PRIVILEGES ON *.* to airavata@'*' IDENTIFIED BY 'airavata';
mysql> FLUSH PRIVILEGES;


6. Check table status

You can check the status of a database table using check table command. Suppose I have a table called
"Host_Descriptor" and I want to check the status of that table.

mysql> check table Host_Descriptor;
+---------------------------------+-------+----------+----------+
| Table                           | Op    | Msg_type | Msg_text |
+---------------------------------+-------+----------+----------+
| persistent_data.host_descriptor | check | status   | OK|
+---------------------------------+-------+----------+----------+
1 row in set (0.00 sec)

This command is quite useful if you have a corrupted table in your database. 


7. Repair a corrupted table

If one of your table being corrupted, you can use the following command to repair it.

mysql> repair table Host_Descriptor;


8. Create a database dump and restore to a new database

In most of the times, you will need to create database dump and restore it to a new database. To create the dump file, you need to use mysqldump script inside bin folder of mysql extracted location.

Suppose I have a database called "persistent_data" and I want to restore it to newly created database called "testDB". "airavata" is an admin user of my database and its password is "airavata".

//to create the dump
140-182-209-114:mysql-5.5.27-osx10.6-x86_64 chathuri$ ./bin/mysqldump -u airavata -pairavata persistent_data > airavatadump.sql
//restore from the created dump
140-182-209-114:mysql-5.5.27-osx10.6-x86_64 chathuri$ ./bin/mysql -u airavata -pairavata testDB < airavatadump.sql


9. Pass a REGEX to select query

In mysql select query, you can pass a regular expression as well.

mysql> SELECT experiment_ID FROM Experiment_Data  WHERE username REGEXP '^admin$';

Note the two characters that are being highlighted in red (^ and $). This will return experiment ID which has a username as admin. You can pass any kind of regular expression between ^ and $ characters. 






No comments: