I am in the midst of upgrading my Ubuntu distro from 10.4 to 12.4. Fearfully, it asked me for root password on MySQL when I was upgrading the distro.
I used to have databases on that server, would they be erased? If so, how could I get them back?
MySQLUbuntuupgrade
I am in the midst of upgrading my Ubuntu distro from 10.4 to 12.4. Fearfully, it asked me for root password on MySQL when I was upgrading the distro.
I used to have databases on that server, would they be erased? If so, how could I get them back?
MySQL has no particular SMTP setup, mechanisms, or drivers whatsoever built in.
However, there are two basic things you can do the kind of monitoring you want.
Option 1 : You Could Monitor the Binary Logs
If binary logs are enabled, you could write a shell script to call mysql and do SHOW MASTER STATUS; If either the filename or filesize changes, something changed. Once detected, you could send out an email expressing that something changed !!!
Try something like this:
FIRST_READ=1
while [ 1 -eq 1 ]
do
mysql -h... -u... -p... --skip-column-names -A -e"SHOW MASTER STATUS" > /tmp/ms.txt
currfile=`cat /tmp/ms.txt | awk '{print $1}'`
currsize=`cat /tmp/ms.txt | awk '{print $2}'`
if [ ${FIRST_READ} -eq 0 ]
then
SOMETHING_CHANGED=2
if [ "${prevfile}" == "${currfile}" ] ; then (( SOMETHING_CHANGED-- )) ; fi
if [ "${prevsize}" == "${currsize}" ] ; then (( SOMETHING_CHANGED-- )) ; fi
if [ ${SOMETHING_CHANGED} -gt 0 ]
then
echo "Something Changed" | mail -s "Something Changed Subject" abc@xyz.com
fi
fi
FIRST_READ=0
prevfile=${currfile}
prevsize=${currsize}
sleep 10
done
Option 2 : You Could Monitor information_schema.tables
You could loop through every table and check its UPDATE_TIME column in information_schema.tables
First collect all table names prepended with database. Then, loop through all the table names and check that entry in information_schema.tables.
Try the following (Any table that changed in the last 10 minutes):
mysql -h... -u... -p... --skip-column-names -A -e"SELECT CONCAT(table_schema,'.',table_name) FROM information_schema.tables WHERE table_schema NOT IN ('information_schema','mysql') AND engine IS NOT NULL" > /tmp/TableNamesToPoll.txt
while [ 1 -eq 1 ]
do
for DBTB in `cat /tmp/TableNamesToPoll.txt`
do
DB=`echo ${DBTB} | sed 's/\./ /g' | awk '{print $1}'`
TB=`echo ${DBTB} | sed 's/\./ /g' | awk '{print $2}'`
NEWUPDATE=`mysql -h... -u... -p... --skip-column-names -A -e"SELECT IFNULL(update_time,NOW() - INTERVAL 100 YEAR) > (NOW() - INTERVAL 10 MINUTE) UpdatedRecently FROM information_schema.tables WHERE table_schema='${DB}' AND table_name='${TB}'"`
if [ ${NEWUPDATE} -eq 1 ]
then
echo "Something Changed in ${DBTB}" | mail -s "Something Changed Subject" abc@xyz.com
fi
done
sleep 5
done
These are just skeleton scripts to detect changes. For option 1, you can do mysqlbinlog against the current binary log and see the SQL that was executed in whatever timeframe you need. For option 2, you can change the SQL to retrieve the datetime stamp of the last update for a given table.
UPDATE 2011-06-29 06:30 EDT
Option 3 : You Could Monitor the general log
Interestingly, you could activate the general log. What's even more intriguing is that you can activate it a MySQL Table. The template to the general log as a table already exists in /var/lib/mysql/mysql as general_log.CSV. Here are the steps:
Step 01) Add these to /etc/my.cnf
[mysqld]
log-output=TABLE
log
Step 02) service mysql restart (general_log is a CSV table after restart)
Step 03) Run these commands to convert the general_Log from CSV to MyISAM
SET GLOBAL general_log = 'OFF';
ALTER TABLE mysql.general_log ENGINE = MyISAM;
ALTER TABLE mysql.general_log ADD INDEX (event_time);
Step 04) Move the general_log file to a huge disk volume that can accommodate a fast growing log table
Example: If you have the following disk layout
[root@iml-db10 ~]# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/vg1-root 117G 3.2G 108G 3% /
/dev/mapper/vg2-data01
1.7T 688G 877G 44% /data
/dev/sdc1 3.6T 36G 3.4T 2% /backup
/dev/sda1 99M 18M 77M 19% /boot
tmpfs 95G 0 95G 0% /dev/shm
none 16G 51M 16G 1% /var/tmpfs
Perform these steps to move the general log table:
mkdir /backup/general_log
mv /var/lib/mysql/mysql/general_log.MY* /backup/general_log/.
chown -R mysql:mysql /backup/general_log
ln -s /backup/general_log/general_log.MYD /var/lib/mysql/mysql/general_log.MYD
ln -s /backup/general_log/general_log.MYI /var/lib/mysql/mysql/general_log.MYI
When do make sure the symlinks exist
[root@db1]# ls -l /var/lib/mysql/mysql/general*
-rw-rw---- 1 mysql mysql 8776 Jun 25 15:53 /var/lib/mysql/mysql/general_log.frm
lrwxrwxrwx 1 root root 35 Jun 25 18:33 /var/lib/mysql/mysql/general_log.MYD -> /backup/general_log/general_log.MYD
lrwxrwxrwx 1 root root 35 Jun 25 18:32 /var/lib/mysql/mysql/general_log.MYI -> /backup/general_log/general_log.MYI
Step 05) Run this SQL command
SET GLOBAL general_log = 'ON';
That's it. You should have the general_log as a MyISAM table
mysql> show create table mysql.general_log\G
*************************** 1. row ***************************
Table: general_log
Create Table: CREATE TABLE `general_log` (
`event_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`user_host` mediumtext NOT NULL,
`thread_id` int(11) NOT NULL,
`server_id` int(10) unsigned NOT NULL,
`command_type` varchar(64) NOT NULL,
`argument` mediumtext NOT NULL,
KEY `event_time` (`event_time`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='General log' DATA DIRECTORY='/backup/general_log/' INDEX DIRECTORY='/backup/general_log/'
1 row in set (0.00 sec)
All you need to do is poll the general_log table every 15 minutes via crontab running this query
SELECT COUNT(1) UpdateCount FROM mysql.general_log
WHERE LOCATE('UPDATE',argument) > 0
AND event_time >= (NOW() - INTERVAL 15 MINUTE);
SELECT COUNT(1) DeleteCount FROM mysql.general_log
WHERE LOCATE('DELETE',argument) > 0
AND event_time >= (NOW() - INTERVAL 15 MINUTE);
WARNING : Entries will pile up quickly. Delete all events keeping the last 3 days. Run this in a crontab every night a midnight
SET @old_log_state = @@global.general_log;
SET GLOBAL general_log = 'OFF';
DELETE FROM mysql.general_log WHERE event_time < NOW() - INTERVAL 3 DAY;
SET GLOBAL slow_query_log = @old_log_state;
Give this a Try !!!
The simplest option is to just back up everything in the filesystem and carry it over.
To find out where your data is, look at the datadir variable with in mysql
test> show variables like 'datadir';
+---------------+-------------------+
| Variable_name | Value |
+---------------+-------------------+
| datadir | /var/lib/mysql/ |
+---------------+-------------------+
1 row in set (0.00 sec)
Note Your actual value may differ.
Then from your shell shut down mysql, back it up and copy it over. Something like:
> sudo service mysql shutdown
> cd /var/lib/mysql
> sudo tar -czf /path/with/enough/space/db.tgz *
Copy your db.tgz over to your new server and uncompress it on your new servers datadir while it is shutdown
Best Answer
Your databases will remain untouched (though of course I can't be liable :) ).
The reason are are being asked for a password could be:
Your new Ubuntu version uses a newer version of MySQL (it does). It needs
root
password so as to run themysql_upgrade
utility, which upgrades internalmysql
schema to fit new versionYou new installation includes new packages/dependencies, for which a new schema should be created. Such packages could be
phpmyadmin
,cacti
,bugzilla
,wordpress
or any others which may require use of a database schema.You should be in particular comfortable with this upgrade since this is from one LTS (Long Term Support) version to another. Not that I would expect database problems with other upgrades, but still, LTS versions are expected to be more "trusted".
Just to conclude, I am using Ubuntu. Usually installing MySQL from external binaries, but sometimes from package. I've never heard of a "data gone lost" problem due to an upgrade.