Mysql

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

Note that by default there are 2 admins, be sure to assign a password to both

mysql> SET PASSWORD=PASSWORD('sql_long_pwd');
mysql> SET PASSWORD FOR root@<hostname>=PASSWORD('sql_long_pwd');

and to not leave the password in clear in you history, that would be a pity...

grep -i pass ~/.mysql_history

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

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.

Lost MySQL root password?

Converted with HTML::WikiConverter::MediaWiki from my old phpwiki site

Using mysqladmin

first kill (not kill -9) the mysql server

kill `cat /mysql-data-directory/hostname.pid`

Restart mysqld with the --skip-grant-tables option

/path/to/safe_mysqld --skip-grant-tables

Set a new password with the mysqladmin password command

mysqladmin -u root password 'mynewpassword'

either stop mysqld and restart it normally, or just flush privileges

mysqladmin -h hostname flush-privileges

now the new password should work.

Using the mysql client:

restart mysqld with the --skip-grant-tables Connect to the mysqld server with

mysql -u root mysql

Update root password like this

mysql> UPDATE user SET Password=PASSWORD('mynewpassword') WHERE User='root';
mysql> FLUSH PRIVILEGES;

now the new password should work.

Misc

Export to CSV

mysql> select * from mytable where ... 
       into outfile '/tmp/test.csv' fields terminated by ';' enclosed by '"' lines terminated by '\n';

Then e.g. importing it into oocalc, choose UTF8, separator=semicolon, "