I was doing a mysql migration from one zone to another machine, in the process upgrading from mysql 5.0.45 (bundled with snv_95) to mysql 5.0.67 (bundled with snv_105), and I'm just making some quick notes for reference.
First off, the migration of the database data is best done with mysqldump. Mysqldump can dump all of the tables live, and it can also be piped into mysql to push the data to the remote system rather than scping the dump across if you prefer.
Obviously you'll want to make sure that your client isn't making changes to the data at the moment of cutover.
Example command:
/usr/mysql/5.0/bin/mysqldump -u root -pshhhhhhh -B dspam | /usr/mysql/5.0/bin/mysql -u test -p -h 192.168.10.5 dspam
Out of the box, mysql is a bit wide open.
mysql> select user,password,host from mysql.user;
| user | password | host |
+-------+-------------------------------------------+-----------------------+ |
|root | | 127.0.0.1 |
| | | db |
|root | | db |
| | | localhost |
|root | | localhost |
This tells us that root can log into mysql from 127.0.0.1, db (hostname), or localhost, and that there are no passwords needed....yikes! There are also two 'anonymous' (no user needed) accounts setup.
At least this only represents a local vulnerability....
First lets drop all the extra accounts.
mysql> drop user ''@'localhost';
mysql> drop user ''@'db';
mysql> drop user 'root'@'127.0.0.1'; (doesn't work anyway, should be localhost)
mysql> drop user 'root'@'db'; (doesn't work anyway, should be db.hostname.domain)
That clears out the obvious ones, next update the root@localhost password.
mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('passwordhere');
And recreate the root@fqdn account.
mysql> grant all privileges on *.* to 'root'@'db.griffous.net' identified by 'passwordhere';
At this point I recommend backgrounding your existing mysql session (ctrl-Z), or using a second ssh session into the server to attempt another parrallel mysql login. If you can't get in, the foregrounded mysql connection is your last remaining chance to fix the problem so it's worth testing before ending that session and locking yourself completely!
As far as server configuration goes, the my-small-cnf.cnf configuration file is used by default, which is designed for systems with less that 64MB of RAM. I would change this to at least the my-medium.cnf which uses up to 128MB. (large uses 512MB btw)
Handy commands
Print all users accounts setup on the server.
select User,Password,Host from mysql.user
Show databases:
show databases;
use database; to open it.
show tables; to view tables in that db
/G at the end of all commands prints listings vertically, which can be easier to read in some circumstances.
mysqldump syntax at it's most simple is:
/usr/mysql/5.0/bin/mysqldump -u root -ppassherewithnospace dbnamehere > outputfilename.
I recommend using -B to specific the database name(s) even if you are just dumping 1 single DB, as it will add lines to the script to create the database on the destination if it doesn't already exist.
scp the output file across, and pump it back in again with a simple
mysql -u root <>
Adding users and access takes the following format:
mysql> grant all privileges on dspam.* to 'dspam'@'taraxen1.griffous.net' identified by 'password here';
To grant access to any host, us the % symbol rather than .*. i.e to 'user'@'%'
To grant access to the entire DB, use on dbname.*. Next is the username, and where they are connecting from. It seems that fqdns are favoured.
Removing users is pretty straight forward with the drop statement:
mysql> drop user 'dspam'@'taraxen1';
Show table type (remember that mysql uses different backends per table)
mysql> show table status
Show table structure:
mysql> describe dspam_stats;
Monitor the innodb engine:
mysql> show engine innodb status \G
Show currently connected users:
mysql> show processlist;
+-----+------+-----------+------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
501 | root | localhost | NULL | Query | 0 | NULL |
No comments:
Post a Comment