MySQL tips

MySQL tips

MySQL tips 150 150 Roderick Derks

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 ~]# 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′

Roderick Derks

Liefhebber van fietsen, van het oplossen van IT puzzels, en van het delen van informatie om anderen te helpen.

All stories by:Roderick Derks

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

    Your Name (required)

    Your Email (required)


    Your Message

      Your Name (required)

      Your Email (required)


      Your Message