Changing the default data directory in Mysql

Had to reconfigure the mysql data directory today. Found this stackoverflow link to be spot on.
stackoverflow-change mysql default data directory ( I really don’t want to be accused of plagiarism)
Reproducing the content here [Just in case the link disappears]

1. Stop MySQL using the following command:

sudo /etc/init.d/mysql stop

2. Copy the existing data directory (default located in /var/lib/mysql) using the following command:

sudo cp -R -p /var/lib/mysql /newpath

3. Edit the MySQL configuration file with the following command:

gedit /etc/mysql/my.cnf

4. Look for the entry for datadir, and change the path (which should be /var/lib/mysql) to the new data directory.

5. In the terminal, enter the command:

sudo gedit /etc/apparmor.d/usr.sbin.mysqld

6. Look for lines beginning with /var/lib/mysql. Change /var/lib/mysql in the lines with the new path. Save and close the file.

7. Restart the AppArmor profiles with the command:

sudo /etc/init.d/apparmor reload

8. Restart MySQL with the command:

sudo /etc/init.d/mysql restart

9. Now login to MySQL, and you can access the same databases you had before.

Cheers !!
The Nonsense Logger !!

Install Mysql in Ubuntu without Internet [Solved]

Today, i had to install mysql in an Ubuntu 12.04 64 bit precise instance. Ideally on an instance connected to the internet, issue the following command

sudo apt-get install mysql-server mysql-client

and you are ready to rock n roll !!

But that’s when your machine is directly connected to the internet. What do you do when you don’t have internet access on the machine?

Some scratching of the head and some more googling led me to this. You have to individualy download the required debian packages on a different machine, transfer them to your target machine and then install them using the dpkg command in the following order. Im sharing it here for self reference and for anyone else who would find this useful.

sudo dpkg -i libnet-daemon-perl_0.43-1_all.deb
sudo dpkg -i libplrpc-perl_0.2020-2_all.deb
sudo dpkg -i libdbi-perl_1.622-1_amd64.deb
sudo dpkg -i mysql-common_5.5.32-0ubuntu0.12.04.1_all.deb
sudo dpkg -i libmysqlclient18_5.5.32-0ubuntu0.12.04.1_amd64.deb
sudo dpkg -i libdbd-mysql-perl_4.021-1_amd64.deb
sudo dpkg -i mysql-client-core-5.5_5.5.32-0ubuntu0.12.04.1_amd64.deb
sudo dpkg -i libterm-readkey-perl_2.30-4build3_amd64.deb
sudo dpkg -i mysql-client-5.5_5.5.32-0ubuntu0.12.04.1_amd64.deb
sudo dpkg -i mysql-server-core-5.5_5.5.32-0ubuntu0.12.04.1_amd64.deb
sudo dpkg -i mysql-server-5.5_5.5.32-0ubuntu0.12.04.1_amd64.deb
sudo dpkg -i mysql-server_5.5.32-0ubuntu0.12.04.1_all.deb
sudo dpkg -i libhtml-template-perl_2.9-2_all.deb

Note : The packages mentioned above are meant specifically for the ubuntu 12.04 precise version. The required packages might vary from version to version. However the list above will give you a general sense of direction in case you land up in a situation like mine !!

Cheers !!
The Nonsense Blogger !!

Mysql – Configuring Access from a different Host

I just configured access to a mysql server from a machine outside the server machine. I find that there are a couple of steps that you need to remember. Just posting it here for my self reference and for any one else who would find it useful.

Enabling access to Mysql server from a different machine requires you to do the following two steps.

1. Mysql has to be configured so that it can bind on a host ip/address ( By default it listens only on localhost)

In my.cnf, under [mysqld] change bind-address to Host IP or Host Name. By default this attribute will be set to localhost. Find below, the relevant section of my.cnf file (Find higlighted the bind-address attribute)

[mysqld]
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
bind-address = 192.168.1.45
key_buffer = 16M
max_allowed_packet = 16M
thread_stack = 192K
thread_cache_size = 8
myisam-recover = BACKUP
query_cache_limit = 1M
query_cache_size = 16M

2. Now restart mysql service using any of the following commands

restart mysql
/etc/init.d/mysql restart

3. Give priviliges to the required users – For access from different hosts

Format : GRANT ALL on databasename.* to username@HostIP identified by ‘userpassword’;
Example : GRANT ALL on test.* to tj@192.168.1.50 identified by ‘tjpassword’;

This will give access to user tj from Host 192.168.1.50 to database test. If you want to see the current priviliges for user tj, use the following command.

Format : show grants for username@hostip
Example : show grants for tj@192.168.1.50;

This will show all the grants given to user tj connecting from 192.168.1.50

Note : In case you encounter the following exception (like i did). Now you know what you’ve got to do right !!

java.sql.SQLException: null, message from server: "Host 'XXXX' is not allowed to connect to this MySQL server"

Hope this helps.

Cheers,
The Nonsense Blogger !!

mysqldump utility – some useful no nonsense stuff

mysqldump is a great mysql utility that can be used for backing up and restoring existing mysql databases. Find below, some of the most useful stuff which can be accomplished using this utility.

I. Back Up and Restore a DB

Format : mysqldump -h domainname -u username -ppassword databasename > dumpfile.sql
Example : mysqldump -h localhost -u root -proot test > testdb_dump.sql

You can restore the database using the following commnad

Format : mysql -hdomainname -u username -ppassword databasename < dumpfile.sql
Example : mysql -hlocalhost -u root -proot test_new < testdb_dump.sql

This will dump the contents of testdb_dump.sql into the database test_new. Only thing that you need to make sure is that test_new already exists in your mysql.

II. Back Up only table structure

Format : mysqldump -d -h domainname -u username -ppassword databasename > dumpfile.sql
Example : mysqldump -d -h localhost -u root -proot test > testdb_dump.sql

The only difference here is the -d switch which isntructs mysql not to dump the data.

III. Back Up multiple databases

Format : mysqldump -h domainname -u username -ppassword --databases database1 database2 ... database n > dumpfile.sql
Example : mysqldump -h localhost -u root -proot --databases test test_new > testAndTest_NewDump.sql

IV. Back Up all databases

Format : mysql -h domainname -u username -ppassword --all-databases > dumpfile.sql
Example : mysql -h localhost -u root -proot --all-databases > allDatabasesDump.sql

V. Back Up a spcific table

Format : mysql -h domainname -u username -ppassword databasse table > dumpfile.sql
Example : mysql -h localhost -u root -proot test test_tabale > testtabledump.sql

For more on mysqldump, refer the official documentation @ : http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html

Cheers,
The Nonsense Blogger !! 🙂