Mysql

Mysql Reference Manual

Basic commands

On sql vserver: installation and assignation of a root password

apt-get install mysql-server
mysql -u root
mysql> SET PASSWORD=PASSWORD('sql_long_pwd');
mysql> FLUSH PRIVILEGES;
mysql> QUIT;

Or shorter:

mysqladmin password sql_long_pwd

Now we have to give the pwd each time we manipulate the db:

mysql -u root -p

Edit /etc/mysql/my.cnf to activate the network availability:

bind-address            = <my_ip>

To create a new db and an associated user with full rights on this db

CREATE DATABASE <database>;
GRANT ALL ON <database>.* TO <db_admin>@<host> IDENTIFIED BY '<db_admin_password_in_clear>';

To shutdown mysql

mysqladmin -p shutdown

To delete a table (be careful!!)

mysqladmin -uroot -p drop <my_table>

To backup a database

mysqldump -uadmin_gallery2 -p -h sql --opt gallery2 > gallery2.sql

To change the password of a user (always combined with a host):

SET PASSWORD FOR username@host=PASSWORD('new_password');

Installation of Mysql-dependant programs

• Gallery
• PhpMyAdmin
• Mediawiki
• Webcalendar
• Php-Syslog-ng
• RSS2Jabber

Backups

cf http://dev.mysql.com/doc/refman/5.0/en/mysqlhotcopy.html

Very simple way to backup small DBs:

/sqlbackups/mydb.sql {
      rotate 5
      daily
      size 10M
      compress
      missingok
      postrotate
              mysqldump mydb >/sqlbackups/mydb.sql
      endscript
}

Recovery

After a violent reboot, I got the following error when accessing a table:

[ERROR] /usr/sbin/mysqld: Table \'./syslog/logs20070909\' is marked as crashed and last (automatic?) repair failed

What I did, following http://www.ooad.org/html-chapter/database-administration.html

#/etc/init.d/mysql stop
Stopping MySQL database server: mysqld.

# myisamchk --update-state /var/lib/mysql/syslog/logs20070909.MYI
Checking MyISAM file: logs20070909.MYI
Data records:   12664   Deleted blocks:       0
myisamchk: warning: Table is marked as crashed and last repair failed
myisamchk: warning: 1 client is using or hasn't closed the table properly
- check file-size
- check record delete-chain
- check key delete-chain
- check index reference
- check data record references index: 1
myisamchk: error: Found 12678 keys of 12664
- check record links
myisamchk: error: Record-count is not ok; is 12678        Should be: 12664
myisamchk: warning: Found      12678 parts                Should be: 12664 parts
MyISAM-table 'logs20070909.MYI' is corrupted
Fix it using switch "-r" or "-o"

# myisamchk --update-state -r /var/lib/mysql/syslog/logs20070909.MYI
- recovering (with sort) MyISAM-table 'logs20070909.MYI'
Data records: 12664
- Fixing index 1
- Fixing index 2
- Fixing index 3
- Fixing index 4
- Fixing index 5
- Fixing index 6
Data records: 12678

# /etc/init.d/mysql start
Starting MySQL database server: mysqld ..
Checking for corrupt, not cleanly closed and upgrade needing tables..

Then I still found another warning in /var/log/syslog:

Sep 10 15:41:12 sql /etc/mysql/debian-start[30715]: Checking for crashed MySQL tables.
Sep 10 15:41:21 sql /etc/mysql/debian-start[30725]: WARNING: mysqlcheck has found corrupt tables
Sep 10 15:41:21 sql /etc/mysql/debian-start[30725]: phpwiki_cartable.page
Sep 10 15:41:21 sql /etc/mysql/debian-start[30725]: warning  : 1 client is using or hasn't closed the table properly

I did a simple check:

#/etc/init.d/mysql stop
# myisamchk --update-state /var/lib/mysql/phpwiki_cartable/*.MYI
#/etc/init.d/mysql start

And now everything seems to be in order.