MySQL tips

This small article might be handy for newbie MySQL administrators (like myself).

Backup and restore of one database
[root@ventoux ~]# mysqldump -u root -p -all [databasename] > /path/filenename.sql

[root@ventoux ~]# scp /path/filename.sql root@galibier.r71.nl:/newpath/

[root@galibier ~]# mysql -u root -p [(other) databasename] < /path/filename.sql

[root@ventoux mysql5]# mysql -u root

Adjust password:

[root@ventoux mysql5]# mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9 to server version: 5.0.18-standard

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.

mysql> update mysql.user set Password=PASSWORD(‘Dit_Is_Je_Password’) where User=’root’;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> \q

Repair a MySQL table (some VERY BASIC steps):

1.) Start admin tool:
# mysql -u root2.)
Connect to db
# connect DBNAME
3.) Repair it and cross fingers
# repair table TABLENAME

Ofcourse you need to replace the DBNAME and TABLENAME with you settings.

Execute a SQL statement from the Linux commandline

# mysql -u root -proot -e “SHOW VARIABLES”|grep -i key_buffer_size

MYSQL statement example:

# SELECT host_name, host_address FROM `host` where `host_register`=’1′