MySQL
From HerzbubeWiki
Contents |
Debian packages
I use MySQL to provide database services on my machine. The following Debian packages need to be installed:
mysql-server mysql-client phpmyadmin
Maintenance user
Installing the MySQL packages creates a maintenance system user
debian-sys-maint@localhost
An automatically generated password is placed in plaintext (!) in
/etc/mysql/debian.cnf
The user is used by
- Maintenance scripts run by cron (e.g. log rotation)
- Init script to stop the server
Configuration
In earlier versions of MySQL, networking by default was disabled in /etc/mysql/my.cnf. It used to be necessary to edit the file and comment the statement skip-networking.
This is no longer the case, nowadays MySQL is configured to listen on 127.0.0.1. In fact, I don't change my.cnf at all!
Users and privileges
Set the initial password for the MySQL root user:
/usr/bin/mysqladmin -u root password '<secret>'
(add the -p option if the password needs to be changed at a later time)
If the password needs to be reset to a known value because it was forgotten:
/etc/init.d/mysqld stop safe_mysqld --skip-grant-tables & mysqladmin -u root flush-privileges password '<secret>' /etc/init.d/mysqld restart
Log in from the command line (the password will be asked interactively):
mysql -u root -p
Allow a host to access all databases:
insert into
host(host,db,Select_priv, Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv)
values('localhost','%','Y','Y','Y','Y','Y','Y');
(note: instead of '%' it is possible to specify just one single database)
Add a new user that is allowed to log in from a specific host:
-- This is the preferred form
create user foo@localhost;
-- An alternative, which might break if the implementation details change
insert into
user (host, user, password)
values('localhost','foo',password('secret'));
Allow an existing user to access a specific database from a specific host
-- This is the preferred form
-- Note: "all privileges" means: all except the "grant" privilege
grant all privileges on <database>.* to foo@localhost;
-- If the database name contains special characters, the db name must appear
-- insided backticks (``); simple quotes or double quotes do not work!
grant all privileges on `drupal614\_herzbube`.* to foo@localhost;
-- An alternative, which might break if the implementation details change
insert into
db (host,db,user,Select_priv, Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv)
values ('localhost','<database>','foo','Y','Y','Y','Y','Y','Y');
-- Yet another alternative, which I believe also creates the user at the same time
grant all privileges on <database>.* to foo@localhost identified by 'secret';
Remove privileges
revoke all privileges on <database>.* from foo@localhost; revoke all privileges on *.* from foo@localhost;
Delete a user
drop user foo@localhost;
Databases
Create a new database
mysqladmin -u -p create <database> mysqladmin -u root -p reload
An alternative is to log in and issue this SQL statement
create database <database>";
Database backup:
mysqldump --add-drop-table -u <user> -p<password> <database> >/tmp/<backup-filename>.sql mysqldump --all-databases -u <user> -p<password> >/var/backups/mysql/all-databases.sql
(the database "mysql" should always backed up because it contains all the administrative information, such as user privileges)
Restore of a database:
mysql -u <user> -p<password> <database> </tmp/<backup-filename>.sql
Character sets
Display all the available character sets and collations:
show character set; show collation;
Display various current character set and collation parameters:
mysql> show variables like 'character_set%'; +--------------------------+----------------------------+ | Variable_name | Value | +--------------------------+----------------------------+ | character_set_client | latin1 | | character_set_connection | latin1 | | character_set_database | utf8 | | character_set_filesystem | binary | | character_set_results | latin1 | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | +--------------------------+----------------------------+ mysql> show variables like 'collation%'; +----------------------+-------------------+ | Variable_name | Value | +----------------------+-------------------+ | collation_connection | latin1_swedish_ci | | collation_database | utf8_general_ci | | collation_server | latin1_swedish_ci | +----------------------+-------------------+
Change the default character set (for new tables) and collation of a database:
alter database foobar default character set utf8 collate utf8_general_ci;
Display the character set currently in use on a table (will display many other parameters as well, look for the "CHARSET" parameter):
show create table foobar;
Change the character set and collation of a table:
alter table foobar convert to character set utf8 collate utf8_general_ci;
Troubleshooting
If you receive the error message "Database failed to execute query <...> 1016: Can't open file: 'sometable.MYI'. (errno: 145)", one of your database tables has become corrupt and needs to be fixed. There are several ways how this can be done:
- simply restart the MySQL server (/etc/init.d/mysql restart); when the server starts up it should automatically fix any corrupt tables
- manually execute the SQL statement repair table; the server does not need to be restarted, but you have to know which database and table you want to fix
- stop the MySQL server, then manually fix the .myi table file in /var/lib/mysql by running the myisamchk command line utility
Important: If you are not sure what you are doing, but especially when you use myisamchk, you should make a backup copy of your database before you attempt to fix the problem!
phpMyAdmin
Apache configuration
phpMyAdmin brings its own Apache configuration file that defines everything necessary to run phpMyAdmin either under an alias path /phpmyadmin, or under its own virtual host.
I have assigned an Apache vhost to phpMyAdmin that is accessible under http://mysql.herzbube.ch/. These are the configuration details:
# --------------------------------------------------------------------------------
# mysql.herzbube.ch
# --------------------------------------------------------------------------------
<VirtualHost *:80>
ServerName mysql.herzbube.ch
ServerAdmin webmaster@herzbube.ch
ErrorLog /var/log/apache2/herzbube.ch/error.log
CustomLog /var/log/apache2/herzbube.ch/access.log combined
DocumentRoot /usr/share/phpmyadmin
Alias /robots.txt /var/www/herzbube.ch/mysql.herzbube.ch/robots.txt
<Directory /usr/share/phpmyadmin/>
Allow from all
<IfModule mod_php5.c>
php_admin_flag engine on
</IfModule>
</Directory>
</VirtualHost>
# --------------------------------------------------------------------------------
# SSL Host
# --------------------------------------------------------------------------------
<IfModule mod_ssl.c>
<VirtualHost *:443>
ServerName mysql.herzbube.ch
ServerAdmin webmaster@herzbube.ch
ErrorLog /var/log/apache2/herzbube.ch/error.log
CustomLog /var/log/apache2/herzbube.ch/access.log combined
DocumentRoot /usr/share/phpmyadmin
Alias /robots.txt /var/www/herzbube.ch/mysql.herzbube.ch/robots.txt
<Directory /usr/share/phpmyadmin/>
Allow from all
<IfModule mod_php5.c>
php_admin_flag engine on
</IfModule>
</Directory>
SSLEngine on
SSLCertificateFile /etc/ssl/certs/herzbube.ch.crt
SSLCertificateKeyFile /etc/ssl/private/herzbube.ch.key.unsecure
SSLCertificateChainFile /etc/ssl/certs/cacert.org.certchain
SetEnvIf User-Agent ".*MSIE.*" nokeepalive ssl-unclean-shutdown
</VirtualHost>
</IfModule>
Web browser configuration
After the upgrade to 2.8.0.3-1, a script named setup.php can be found in
/usr/share/phpmyadmin/scripts
This script allows various configuration options for the Web appearance of phpMyAdmin. The script is protected by basic HTTP authentication configured in
/etc/phpmyadmin/apache.conf
The configuration points to the password file
/etc/phpmyadmin/htpasswd.setup
To change the user database from password file to LDAP, modify /etc/phpmyadmin/apache.conf.
Old configuration:
<IfModule mod_authn_file.c> AuthType Basic AuthName "phpMyAdmin Setup" AuthUserFile /etc/phpmyadmin/htpasswd.setup </IfModule> Require valid-user
New configuration:
<IfModule mod_authnz_ldap.c> Include osgiliath-ldap.conf AuthName "phpMyAdmin Setup" AuthType Basic AuthBasicProvider ldap AuthzLDAPAuthoritative off </IfModule> Require user admin
Upgrade from version 4:2.11.8.1-3 to 4:3.3.7-1
The upgrade has some hickups:
- phpmyadmin now requires its own database
- The Debian package suggests to manage this with dbconfig-common -> that's ok
- Package installation requires a "DB upgrade"; the upgrade process then fails because the database does not exist yet -> the fix is to manually "create database phpmyadmin" while logged in as DB admin (root), then to retry the DB upgrade
- /etc/phpmyadmin/apache.conf also needs to be manually updated; instead of a single setup script (setup.php), there is now an entire directory (
/usr/share/phpmyadmin/setup) that needs protection -> simply copy the LDAP authentication/authorization block that was already in use
