Mysql

2007-09-10T13:52:46Z

213.219.185.103: /* Backups */

[http://www.mysql.org/doc/refman/5.1/en/index.html 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
==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.

YobiWiki - User contributions [en]

Mysql