This is a survival guide to MYSQL on FreeBSD and Linux. This guide is based in MySQL 5.1.x, versions 5.1.6 and .7 made significant changes to query logging which are noted.
We use FreeBSD 5.x, 6.x and Fedora Core. Where there are differences they are marked [FC] and [BSD] - lines with no identifier either represent an oversight or no difference! We also had to build a trvial PHP MySQL Web Viewer 'cos we could not find one.
For Linux (Fedora Core) use the following RPMs:
to be supplied
For FreeBSD use the following commands to install from the ports collection:
cd /usr/ports/databases/mysqlxx-server make install clean
Where xx is the MySQL version number. By default FreeBSD will install the client as well for the same version (but does not deinstall by default).
FreeBSD - use portupgrade (after doing a cvsup for ports-databases as a minimum) to install the latest release of the version currently installed. Assume you have version 4.1.3, then portupgrade would update to, say, 4.1.4 - it will not upgrade to version 5.1.
To upgrade to a new version you need to make deinstall first as in this sequence:
cd /usr/ports/databases/mysql41-server make deinstall # if the client is also installed (default) you will need to # separately deinstall as shown cd ../mysql41-client make deinstall # then install new version cd ../mysql51-server make install clean # which will also install the same version of the client
make deinstall does NOT remove databases from /var/db/mysql you will be delighted - and relieved - to hear.
After a new installation of MySQL the following should be performed:
# check that install had added a user mysql (it does by default) id mysql # if not add these groups by running this script [bsd] /usr/local/bin/mysql_install_db --user=mysql [fc] /usr/bin/mysql_install_db --user=mysqlThe normal install process should do this by default but running it again has no negative effect on a new install.
After an upgraded installation of MySQL the following should be performed:
# normal command for a password protected root account [bsd] /usr/local/bin/mysql_upgrade --password=root-password [fc] /usr/bin/mysql_upgrade --password=root-password # on MySQL 5.1.15 this consistently failed with access denied errors # to bypass the problem the server was stopped and restarted with mysqld_safe --user=mysql --skip-grant-tables & # then (no password required) mysql_upgrade # then stop and start server with normal options: mysqld_safe --user=mysql &
After installation - and setting the initial passwords - you should consider a number of security issues:
Disable Remote Access: By default MySQL is configured to allow client access from remote hosts using port 3306 (configurable in my.cnf). If client access is only required from the server (localhost) then networking access should be disabled by adding (or uncommenting) the following line in the my.cnf file:
Remove the Anonymous Account: The anonymous account allows users to login to mysql without specifying a user name. If this is not required it can be disabled using:
mysql -u root - p [prompts for password] > DELETE FROM mysql.user WHERE User = ''; > FLUSH PRIVILEGES;
Limit any root Login: To limit any root login attempts to localhost only do the following:
mysql -u root - p [prompts for password] > DELETE FROM mysql.user WHERE user = 'root' AND host = '%' > FLUSH PRIVILEGES;
The same process can be used for any user account.
Change the name of root: This is 'security by obscurity' but forces any attack to first find the name of the root account and then the password - arguably doubling the scale of the attacker's challenge. To change the name of the root account to 'thingy' do the following:
mysql -u root - p [prompts for password] > UPDATE mysql.user SET user='thingy' WHERE user='root'; > FLUSH PRIVILEGES;
Next login must use mysql -u thingy -p.
Defines the standard installation locations when using RPMs (Linux) or the Port system (FreeBSD). Two key variables control the location process DATADIR (location of the database files and most logs) and BASEDIR (where the applications are located). The run-time value of these variables, and tons of others) may be read using mysqladmin variables (with appropriate passwords as required).
All applications and utilities are stored in:
[FC] /usr/bin [BSD] /usr/local/bin
Options may be specified on the command line (preceeded with two dashes (--) or in a configuration file or any combination. The standard configuration file is my.cnf (*nix) or my.ini (windows). The location of the file is looked for in:
/etc/my.cnf then /usr/local/etc/my.cnf then home-dir/my.cnf # or may be defined on the command line using --defaults-file=/path/and/file/name (all options) # or --defaults-extra-file=/path/and/file/name (additional options)
defined in my.cnf. Defaults to:
[FC] /var/run/mysqld/mysqld.pid [BSD] DATADIR/FQDN.pid (default = /var/db/mysql/FQDN.pid) # where FQDN = fully qualified domain including the host part # example FQDN = host.example.com.
MySQL stores its databases at the following default locations:
# following are default locations but may be overridden using # the datadir parameter in my.cnf [fc] /var/lib/mysql/ - all log files (default location) [fc] /var/lib/mysql/mysql/ - mysql system database and tables [fc] /var/lib/mysql/database-name/ - files for database-name [bsd] /var/db/mysql/ - all log files (default location) [bsd] /var/db/mysql/mysql/ - mysql system database and tables [bsd] /var/db/mysql/database-name/ - files for database-name # files named table-name.frm contain the table-name defintion # files named table-name.MYD contain the table-name data # files named table-name.MYI contain the table-name index(es)
The default install places in /usr/local/share/mysql a number of my.cnf prototype files (my-xx.cnf) select the most suitable and copy to make operational (my.cnf may be located in a number of places):
cd /usr/local/share/mysql cp my-xxx.cnf /etc/my.cnf
Manual section 5.2. The type and location of the various logs may be defined in my.cnf (my.cnf locations) or on the command line. The default location for all log files is the DATADIR (BASEDIR in 5.x?) ([BSD]/var/db/mysql):
Logging changed after 5.1.6 and again in 5.1.7 to allow the possible use of mysql system tables as looging destinations (and these are now the default location). Directives that control the functionality are --log and --log-output on the command line or log and log_output in my.cnf as defined in the table below:
| - | log= | log=/path/file ----------------------------------------------------------------- - | TABLE | TABLE | TABLE log-output=FILE |FQDN.log | FQDN.log | /path/file log-output=TABLE | TABLE | TABLE | TABLE log-output=NONE | NONE | NONE | NONE log-output=FILE,TABLE | TABLE | TABLE+FQDN.log | TABLE+/path/file log-output=FILE,NONE | NONE | NONE | NONE # where FQDN is the fully qualified host name e.g. host.example.com.
my.cnf logging directives in the [mysqld] section.
# my.cnf log directives in [mysqld] section log=[/path/and/file/name] (connections and SQL statements) not defaulted # if no file name given defaults to DATADIR/FQDN.log log-output[=FILE | TABLE | NONE] (see above) log-error=/path/and/file/name (defaults to DATADIR/FQDN.err) log-bin=/path/and/file/name (not defaulted) # if no file name defaults to the DATADIR/hostname-bin.seq-no # if an extension is given in log-bin it is silently discarded max_binlog_size=2m # forces binary log swap when file size reaches 2 meg. log-slow-queries=/path/and/file/name (not defaulted) #if no name given defaults to DATADIR/hotname-slow.log # Notes and keys-words used in the above descriptions: # FQDN = fully qualified name of the host including the domain part # example FQDN = host.example.com # hostname = the name of the host without the domain part # seq-no = is an incrementing sequence number
Mysql can build up serious log files. There are two major log types to worry about. The General or Slow Query log (log or log-slow-queries options in my.cnf) and Binary logs (log-bin in my.cnf). Foir Linux Redhat/Fedora Core users there is a script supplied in /usr/local/share/mysql/log-file-rotate which is automatically installed and which apperently handles both Genral Query (and Slow Query) and Binary logs.
For FreeBSD to rotate the Slow or General Query log the following can be added to /etc/newsyslog.conf:
# rotates the mysql logs when they reach 1 meg # and keeps 7 copies /var/db/mysql/file-name.log mysql:mysql 600 7 1000 * J /var/db/mysql/file-name.err mysql:mysql 600 7 1000 * J
Alternatively from version 5.1.12 the log can stopped and started from mysql admin without stopping the server by doing the following:
# login to mysql mysql -u root -p (prompts for password) SET GLOBAL general_log = 'OFF'; # log file may now be renamed/moved/deleted # then restart logging SET GLOBAL general_log = 'ON';
Finally the MySQL manual states that you can simply rename (or delete) the log file and then FLUSH LOGS:
cd /path/to/datadir mv log-file.name log-file.name.old # login to mysql mysql -u root -p (prompts for password) FLUSH LOGS; # creates a new file with log-file.name
Binary logs are created when the log-bin option appears in the my.cnf file. The option expire_log_days= can be used to create a new log file after the defined number of days. The old files are not deleted. The binary log files can also me manually rotated using:
# login to mysql mysql -u root -p (prompts for password) FLUSH LOGS; # creates an new binary log file but does not # delete the old one OR mysqladmin flush-logs
Note: FLUSH LOGS; starts a new binary log file with the next sequence number, starts a new error log after renaming the current one by adding -old to the file name but only closes and reopens the General or Slow Query logs.
The preferred method to startup MySQL is using mysqld_safe:
mysqld_safe --user=mysql & # where & causes it to detach from the console # or using standard scripts [fc] /etc/rc.d/init.d/mysql-server start [bsd] /usr/local/etc/rc.d/mysql-server start
[BSD] There is a startup script in /usr/local/etc/rc.d/mysql-server.sh (laster versions seem to be ommitting the .sh suffix) but enable mysql in rc.conf by adding:
mysql_enable="YES" mysql_args="--user=mysql" # and any other argument required
[FC] The normal install places a control script in /etc/rc.d/init.d/mysql which supports start|stop|restart. To load at boot time this script must be invoked from the relevant run level by adding:
ln /etc/rc.d/init.d/mysql-server /etc/rc.d/rcX.d/KYYmysql ln /etc/rc.d/init.d/mysql-server /etc/rc.d/rcX.d/SYYmysql # where: # YY is arbitrary but defines the order in which the # script is invoked relative to all others # X defines the run level e.g. # 3 is multi-user no GUI # 5 is multi-user with GUI (X-windows)
[bsd] killall mysqld # OR [fc]/etc/rc.d/init.d/mysql-server [stop | start | restart] [fc]service mysql-server [stop | start | restart] [bsd]/usr/local/etc/rc.d/mysql-server.sh [start | stop | restart] # OR stop using long manual method [bsd] more /var/db/mysql/host_name.pid [gives xxx = pid] # then kill xxx # OR another manual method ps ax|grep mysql [gives xxx= pid] # then kill xxx # OR mysqladmin -u root -p shutdown [prompts for password]
This section covers common functions using mysql. Commands are terminated are with a ';' and the mysql session may be terminated with either exit or \q. The sequences all assume that the operations are performed as the root user (called root for convenience) and that a password has been assigned.
mysqladmin commands are defined in manual section 8.9.
# single-line but insecure method (password in clear) mysql [-h hostname] -u username -ppassword # no space between p and password in this version of command # -h is optional (defaults to localhost) # OR a variation in syntax (still insecure) mysql [-h hostname] -u username --password=password # OR secure method mysql -u root -p [will prompt for password] # password prompts on following line with no echo # to terminate mysql session use 'exit' or \q
Manual section 13.1.3 (database) 13.1.5 (table). Database name can also be referred to as the DSN (Data Source Name).
mysql -u root -p [will prompt for password] CREATE DATABASE database-name; CREATE TABLE database-name.table-name (field1, field2,..); \q # OR mysqladmin -u root -p create database-name [will prompt for password]
Manual sections 13, 188.8.131.52, 184.108.40.206
mysql - root -p [will prompt for password] show databases; # display tables in database USE db-name; show tables; # OR show tables from db-name; # display fields in table USE db-name; show columns from table-name; # OR show fields from table-name; # [fields is synonym for columns] # OR show columns from table-name from db-name; # OR show columns from db-name.table-name; # to display data you must use an SQL query like SELECT field1, field2 FROM table-name [WHERE clause]; # to delete data you must use a simple SQL query like DELETE FROM table-name WHERE field-name="unique data"; # or a more complex SQL query like DELETE FROM table-name WHERE ( field1="data" AND field2="other data"); exit | \q
The relatively innocuous word DROP means DELETE (as in gone, forever) use with extreme caution. Manual section 13.1.6 (database),13.1.7 (table index) 13.1.8 (table).
mysql - root -p [will prompt for password] # delete database DROP DATABASE database-name; # delete table index DROP index index-name ON database-name.table-name; # delete table DROP TABLE database-name.table-name; exit | \q
Manual section 5.8.2.
mysql - root -p [will prompt for password] # add user # grants privileges on all databases GRANT ALL PRIVILEGES ON *.* TO 'name'@'localhost' IDENTIFIED BY 'password' WITH GRANT OPTION; # grants privileges on specific database (db must exist) GRANT ALL PRIVILEGES ON db-name.* TO 'name'@'localhost' IDENTIFIED BY 'password' WITH GRANT OPTION; # use of 'localhost' limits access to local machine only # use 'hostname' or 'ip' if external access is required # or '%' if any host is valid # delete user DROP USER user-name[,user-name2...] # where user-name takes the form 'user-name'@'host-name' # if simple user-name format used assumes 'user-name'@'%' # where % is the wildcard and means any host # display users USE mysql; SELECT user, host from user #lists all users and the valid hostnames exit | \q
MySQL supports commands to check and repair tables within a database. While they do not always fix problems they can be remarkably effective - once you identify the failing table. Manual section 12.5.
mysql - root -p [will prompt for password] # check table CHECK TABLE table-name [option]; # where options may be # FOR UPGRADE | QUICK | FAST | MEDIUM | EXTENDED | CHANGED # default option is MEDIUM # EXTENDED can take a long time # repair table # requires SELECT and INSERT priviliges REPAIR TABLE table-name [option]; # where option may be: # QUICK EXTENDED USE_FRM # QUICK - only index tree # EXTENDED - row-by-row # USE_FRM - if .myi corrupt/lost (consult manual before using) # CAUTION: make sure you have a current database archive before using # REPAIR \q
Assuming you are using the MyISAM DB engine (default) you can also use the myisamchk utilty (see manual section 4.6.3 - the server must be stopped before running this utility.
Manual section 220.127.116.11 (tutorial) and 18.104.22.168. Set initial password for root:
# to set the initial password mysqladmin -u root password new-password # if remote access is allowed also do this mysqladmin -u root -h host-name password new-password # OR to set the initial password via mysql mysql -u root mysql SET PASSWORD FOR 'root'@'localhost'=PASSWORD("new-password"); # if remote access allowed also do this SET PASSWORD FOR 'root'@'host-name'=PASSWORD("new-password"); # set password for anonymous account (use different password) SET PASSWORD FOR ''@'localhost'=PASSWORD("anon-password"); # if remote access allowed also do this SET PASSWORD FOR ''@'host-name'=PASSWORD("anon-password"); exit | \q
To change password (Manual section 22.214.171.124):
# command line mysqladmin -u user-name -p password new-password [prompts for password] # OR via mysql mysql -u root -p[prompts for password] SET PASSWORD FOR 'user-name'@'host-name'=PASSWORD("new-password"); FLUSH PRIVILEGES; # above necessary for new password to take effect exit | \q
To reset the MySQL password without knowing the password (you have forgotten it or because the person who set-up the MySQL system is (a) on vacation (b) left the company (c) died):
# stop mysql using any technique here # then restart with following command mysqld_safe --user=mysql --skip-grant-tables & # set new password mysqladmin -u root flush-privileges password "newpassword" # stop and re-start mysql with normal command mysqld_safe --user=mysql # OR via mysql mysql -u root -p[prompts for password] SET PASSWORD FOR 'user-name'@'host-name'=PASSWORD("new-password"); FLUSH PRIVILEGES; # above necessary for new password to take effect exit | \q
If you only want to save the database schema (the SQL commands that build the tables within the database) and not the data then use this command on a existing database.
# assume database name is mybd mysqldump -d -u root -p mydb > mydb.sql [prompts for password]
The -d option means no data will be saved. If the data is required use the commands shown under Archive and Restore below.
To import and create a database schema from the saved file use:
mysql -u user-name -p db-name < mydb.sql [prompts for password] # omit db-name if sql file creates database, if not db-name must exist
mysqldump is the generic program used to archive a MySQL database. Specific databases or all databases may be saved with this program.
mysqldump -u root -p mydb > mydb+data.sql [prompts for password] # saves only the database mydb to the file mydb+data.sql # if you want to run this from a script without typing # the password, add the password after the -p WITHOUT A SPACE mysqldump -u root -pmypassword mydb > mydb+data.sql # to save all databases use the -A argument (does not need a database name) mysqldump -A -u root -p > mydb+data.sql [prompts for password] # OR no password prompt for a script mysqldump -A -u root -pmypassword > mydb+data.sql # to run as a cron job with date based file use a script like this #!/bin/sh - NOW=$(date +%b-%d-%Y) export NOW mysqldump -A -u root -pmypassword > $NOW.sql # NOW gives format Feb-12-2007 # then place script in your periodic directory for the # frequency required (daily, weekly, monthly) # OR add to your cron tabs location (/var/cron/tabs) # in which case you will need this format SHELL=/bin/sh 15 2 * * * /path/to/your/script # runs daily at 2:15 AM # consult man cron for more info
Saves the schema and data for the databases defined.
To restore a database when saved using mysqldump above:
mysql -u root -p mydb < mydb+data.sql [prompts for password]
Restores the schema and data for the database mydb.
If you want to move databases to another server while simultaneously providing service then the problem becomes quite complex and will involve replication. First you would set up a replication server on the new system then, when fully operational, change the master to become the new server. Finally you can retire the old server. This scenario is not further described.
If you are simply moving databases and have the ability to shutdown service then you have two choices:
Use mysqldump on the current server, copy the resulting file and restore on the new server for each database using the resoration procedure described above. Since this uses a text based interchange method it is probably the safest and will allow for any changes in MySQL versions.
If your versions of MySQL are compatible (read the various MySQL release notes carefully) you can simply copy the various database binary files to create an exact copy of your current configuration on the new server. This includes copying the MySQL operational database (in /var/db/mysql/mysql or /var/lib/mysql/mysql). This method has the overriding advantage than it does not require knowledge of MySQL passwords - it can done entirely as a black-box transfer. This method is described in detail below.
Note: Assuming that your MySQL configuration is supporting an Internet based service such as a web server, FTP server etc. and assuming you are also changing the IP address of the new server (perhaps also the host name) the actual time to make the transition to the new server is determined by the TTL (Time to Live) value in the DNS records that point to the target service, for example the DNS A record for www.example.com. If these TTLs are long, for example two days is a typical value, then the actual transition will take up to this period of time. For reasons of database intergrity you MUST keep the old server off-the-air during this period and hence worst case users would see a dead service for up to two days in this example. To reduce this out-of-service delay during the transition period you may want to consider changing the DNS TTL values involved prior to making the change. Assume that you have a 48 hour TTL on the DNS A records, then at least 48 hours before making the server change, modify the DNS A record TTL to say 1 hour. While this puts an increased load on the DNS it has the effect of reducing the caching time in remote DNS servers to 1 hour. When you finally cut to the new server and modify the IP address you can also reset the TTL to the original value, say 48 hours. This procedure has the effect of reducing the out of service time to 1 hour for worst case users.
The following procedure was used to transfer binary files from MySQL version 5.1.17 on the old server to 5.1.26 on a new server.
Copy the my.cnf file from the old to the new server (this can be done while fully operational). If you have a modified startup script you should also copy this to the new server as well.
load mysql on the NEW server using your normal method such as:
# using standard scripts [bsd] /usr/local/etc/rc.d/mysql-server start [fc] /etc/rc.d/init.d/mysql-server start # confirm it has loaded successfully ps ax|grep mysql # debug if not loaded
We found this dummy load was necessary to allow mysql to perform some initialization including establishing directory structures, permissions, paths and certain variables but it is entirely possible that we could have achieved the same result by using the datadir=path/to/db/files parameter of my.cnf. However the dummy load worked perfectly for us without any need to modify my.cnf.
Shut down mysql on the NEW server:
# using standard scripts [bsd] /usr/local/etc/rc.d/mysql-server stop [fc] /etc/rc.d/init.d/mysql-server stop
Now you are ready to perform the transfer of server so to avoid copying unnecessary files you may want to flush logs and prune log files as appropriate. Now shutdown mysql on the OLD server. From this point on for reasons of database integrity you cannot restart service on the OLD server unless you decide to abandon the transfer process. Copy all files MySQL binary files using a suitable process such as scp, or via portable media, from the OLD server to the NEW server:
# copy all files from the normal location [fc] /var/lib/mysql [bsd] /var/db/mysql # after copying check that the appropriate permissions are set # on all directories and files # in most cases this should mean all the files are # owned by user = mysql and group = mysql # access permissions are typically 0660 # if not use chown and chmod to fix chown -R mysql:mysql /path/to/mysql/datafiles chmod -R 0660 /path/to/mysql/datafiles # You may want to run a check on all files for integrity myisamchk --silent /path/to/mysql/data/files/*/*.MYI
Start MySQL on the NEW server, confirm it is running and that you can login to MySQL using the same passwords that were valid on the OLD server. Perform any other appropriate tests to confirm the new server is fully operational and a faithful replica of the OLD server.
If the MySQL versions are different between the old and new servers you should run mysql_upgrade as shown:
# with mysql running on NEW server mysql_upgrade -u user-name -p
Change your DNS records as appropriate to point to the new server and change any modified TTL values if you used the process described above. Reload the zone (or restart DNS). Wait for transactions to start arriving at the new server.
Problems, comments, suggestions, corrections (including broken links) or something to add? Please take the time from a busy life to 'mail us' (at top of screen), the webmaster (below) or info-support at zytrax. You will have a warm inner glow for the rest of the day.
If you are happy it's OK - but your browser is giving a less than optimal experience on our site. You could, at no charge, upgrade to a W3C standards compliant browser such as Firefox