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, "