phpMyAdmin upload and fix old database

Yes, because you wanted it, two posts in a day about computing. Previously I told you the tale of the phpMyAdmin server on CentOS 7. Now I will upload the database from the old CentOS 6 into the new CentOS 7, and afterwards, fix it. Tachan! Let’s begin.

On “a” we have a working phpMyAdmin system. We can log in as root, etc. Let’s say even we have a working database, but we want to replace it completely. On “b” we have mounted the old system as /data_temp, as a separated disk. The whole system can be accessed as an extra drive. Logic tells me that we need to stop mysql, backup something like “the mysql” folder, and copy the “old” one in place. Let’s do it

root@a ~ ## > service mysqld stop
               Stopping mysqld: [ OK ]
root@a ~ ## > cd /var/lib/
root@a /var/lib ## > mv mysql/ mysql.original/
root@a /var/lib ## > 
rsync -av root@b:/data_temp/var/lib/mysql/ /var/lib/mysql/

Now we start mysql again.

root@a ~ ## > service mysqld start

And we try to login on phpMyAdmin. Eureka! The old database from”b” is there! What could be the problem? For example, it’s stupid but the database from b had another password, that was not the one we set up in a. Fortunately, I was able to recall it. And we found out that one table was corrupted!

ScopeEMData-broken

Then what? We google a little and find out How to Repair MySQL Database Tables. Basically, you select the table on the upper menu (on the picture, it will be “leginondb”) and from the foldable menu at the bottom of the page, we select “Repair table”. There are solutions via the command line also, but since this one works like a charm, I decide not to try the others. The result, after the reparation, looks like this: ScopeEMData-repaired

At last but not at least, I want to clean the logs. Since we’ve been using the old database for a year without really taking care of it,  a lot of binary logs are there. The question is, is it safe to delete mysql-bin files ? We’ll never know until we try it. This I do via command line, as indicated here.

root@a ~ ## > mysql -u root -p
Enter password: 
Welcome to the MySQL monitor. Commands end with ; or \g.
...bla bla bla..

mysql> RESET MASTER;
Query OK, 0 rows affected (1.85 sec)

mysql> exit
Bye

I check and not only they are gone, but the system seems to run much more smoothly. Yes I know. Enough database management for a day. Have a nice day!

Advertisements

About bitsanddragons

A traveller, an IT professional and a casual writer
This entry was posted in bits, centos, linux. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s