linux, Mariadb, mysql, Ubuntu

How I screwed MySQL install with MariaDB and recovered

Broken packages are mess! I recently installed MariaDB to give it a try. After testing, I decided to uninstall it for now and maybe come back later. I was careless while removing MariaDB and ended up messing MySQL configs (MariaDB runs on top of MySQL). Things were only to get worse from here. A high priority task pops up that requires me to access MySQL. Unable to access it, I decide to completely remove MySQL and do a fresh install – basically a hard reset. If only things were that simple in real world. By this time my MariaDB was half removed, MySQL configs were a mess and I was now about to remove MySQL completely. Here’s what happened:

Alright, maybe I did not uninstall MariaDB correctly. Lets try removing that once more:

Same error! Ok, I clearly screwed MySQL. I should try installing it back. Lets see how that goes:

Unmet dependencies? Ok! I know what to do in such cases:

Nothing! That’s scary. There’s no way forward (installing) and there’s no way backward (uninstalling). I am basically stuck!

Maybe autoclean is a good idea? Sadly no difference.

I noticed both MySQL and MariaDB remove commands were exiting because of ‘mysql-server-5.6’, so fixing it was my best bet. I also noticed the error raised while removing mysql-server was from file `/var/lib/dpkg/info/mysql-server-5.6.postrm` as evident in following line:

/var/lib/dpkg/info/mysql-server-5.6.postrm: line 53: /usr/share/mysql-common/configure-symlinks: No such file or directory

dtdm

So what if I comment the line that’s raising this error? Risky, but desperate times – desperate measures. After creating a backup of the file, I edited it to remove the line that raised the error and retried. Here’s what happened :

Finally! Editing the file worked and I was able to remove both packages. Next I fresh installed mysql-server and it worked perfectly. What a relief! 🙂

Advertisements
Standard
mysql

Moving complete mysql from one server to another without mysqldump

Whenever it comes to transfer database (mysql) from one server to other, the first obvious choice that comes to one’s mind is mysqldump. All you need to do is run the command:

mysqldump -u root -p --opt [database name] > [database name].sql

It will output a sql file for your database. Scp the sql file to your new servers, create a database with the same name in the new machine and run this:

mysql -u root -p newdatabase < /path/to/newdatabase.sql

All is well unless your database is of limited size. But suppose your database is in GBs meaning you’re f’ckd!! This method will still work but it will take years (not literally) to generate all the tables and fields in the new database using the dumped sql file. Also it will eat up a lot of resources on your new server.

The alternative is rather way too simple. All you need to do is go to your mysql data directory. If you are using ubuntu, most probably you are looking for this location:

/var/lib/mysql/

Before proceeding ahead, you may want to stop your mysql server. Just run this:

sudo service mysql stop

Also as we are going to access files that need root permissions, better do:

sudo bash

We will now zip all the contents of the mysql data directory.

zip -r completemysql.zip /var/lib/mysql/*

This is will create a zip file containing all you mysql data. Now scp this to the new servers (or machine).
Once you ssh to your new server with the zip file already t transferred in its home. Unzip it in a directory(we name it ‘database’)

unzip completemysql.zip -d database

Now delete all the log files from the unzip location ( ~/database in our case).
It should look something like:

rm ib_logfile0 ib_logfile1

Before proceeding ahead we need to stop the mysql server. Run:

sudo service mysql stop

to confirm the mysql server stopped do:

mysqladmin -u root -p status

If this gives an error, it means the mysql server is not running, which is what we want in our case.
Now copy all the files from ~/database directory to the mysql data directory of this machine.

cp -R ~/database/* /var/lib/mysql/

We are almost there, if at this moment you start your mysql server and try show databases it will list all your databases from the previous server. However when you try to access them, you may not be able to do so and get following permission error :

ERROR 1018 (HY000): Can't read dir of './<your_database_dir>/' (errno: 13)

This is because the owner of the copied files is the ‘root’ and we need to make it ‘mysql’. We can resolve this by:

chown -R mysql:mysql /var/lib/mysql/
chmod -R 755 /var/lib/mysql/

Now start the mysql server and its done.

sudo service mysql start

cheers!!

Standard