MySQL Reference Manual for version 3.23.15-alpha. - 20 Solving some common problems with MySQL Go to the first, previous, next, last section, table of contents. 20 Solving some common problems with MySQL 20.1 Database replication One way replication can be used both to increase robustness and speed. For robustness you have two systems and switch to the backup if you get problems you witch to the backup. The extra speed is achieved by sending a part of the non updating queries to the replica server. Of course this only works if non updating queries dominate, but that is the normal case. One way replication is planned for the near future. This will be implemented so that slave servers will be synchronized with low priority updates and delayed inserts up to date (this will give readers higher priority than writers). MySQL doesn't (yet) have database replication, but here are some info on how do to it. The most general way to replicate a database is to use the update log. See section 20.3 The update log. This requires one database that acts as a master (to which data changes are made) and one or more other databases that act as slaves. To update a slave, just run mysql < update_log. Supply host, user and password options that are appropriate for the slave database, and use the update log from the master database as input. If you never delete anything from a table, you can use a TIMESTAMP column to find out which rows have been inserted or changed in the table since the last replication (by comparing to the time when you did the replication last time) and only copy these rows to the mirror. It is possible to make a two-way updating system using both the update log (for deletes) and timestamps (on both sides). But in that case you must be able to handle conflicts when the same data have been changed in both ends. You probably want to keep the old version to help with deciding what has been updated. Because replication in this case is done with SQL statements, you should not use the following functions in statements that update the database; they may not return the same value as in the original database: DATABASE() GET_LOCK() and RELEASE_LOCK() RAND() USER(), SYSTEM_USER() or SESSION_USER() VERSION(), CONNECT_ID() All time functions are safe to use, as the timestamp is sent to the mirror if needed. LAST_INSERT_ID() is also safe to use. 20.2 Database backups Because MySQL tables are stored as files, it is easy to do a backup. To get a consistent backup, do a LOCK TABLES on the relevant tables. See section 7.26 LOCK TABLES/UNLOCK TABLES syntax. You only need a read lock; this allows other threads to continue to query the tables while you are making a copy of the files in the database directory. If you want to make a SQL level backup of a table, you can use SELECT INTO OUTFILE. Another way to backup a database is to use the mysqldump program: See section 13.4 Dumping the structure and data from MySQL databases and tables. Do a full backup of your databases: shell> mysqldump --tab=/path/to/some/dir --opt --full You can also simply copy all table files (`*.frm', `*.MYD' and `*.MYI' files), as long as the server isn't updating anything. The script mysqlhotcopy does use this method. Stop mysqld if it's running, then start it with the --log-update[=file_name] option. See section 20.3 The update log. The update log file(s) provide you with the information you need to replicate changes to the database that are made subsequent to the point at which you executed mysqldump. If you have to restore something, try to recover your tables using myisamchk -r first. That should work in 99.9% of all cases. If myisamchk fails, try the following procedure: (This will only work if you have started MySQL with --log-update. See section 20.3 The update log.) Restore the original mysqldump backup. Execute the following command to re-run the updates in the update logs: shell> ls -1 -t -r hostname.[0-9]* | xargs cat | mysql ls is used to get all the update log files in the right order. You can also do selective backups with SELECT * INTO OUTFILE 'file_name' FROM tbl_name and restore with LOAD DATA INFILE 'file_name' REPLACE ... To avoid duplicate records, you need a PRIMARY KEY or a UNIQUE key in the table. The REPLACE keyword causes old records to be replaced with new ones when a new record duplicates an old record on a unique key value. 20.3 The update log When started with the --log-update[=file_name] option, mysqld writes a log file containing all SQL commands that update data. If no file name is given, it defaults to the name of the host machine, If file name is given, but it doesn't contain a path the file is written in the data directory. If file_name doesn't have an extension, mysqld will create log file names of type file_name.#, where # is a number that is incremented each time you execute mysqladmin refresh or mysqladmin flush-logs, the FLUSH LOGS statement, or restart the server. If you use the --log or -l options, mysqld writes a general log with a filename of `hostname.log', and restarts and refreshes do not cause a new log file to be generated (although it is closed and reopened). In this case you can copy it (on Unix) by doing: mv hostname.log hostname-old.log mysqladmin flush-logs cp hostname-old.log to-backup-directory rm hostname-old.log By default, the mysql.server script starts the MySQL server with the -l option. If you need better performance when you start using MySQL in a production environment, you can remove the -l option from mysql.server or change it to --log-update. Update logging is smart because it logs only statements that really update data. So an UPDATE or a DELETE with a WHERE that finds no rows is not written to the log. It even skips UPDATE statements that set a column to the value it already has. If you want to update a database from update log files, you could do the following (assuming your update logs have names of the form `file_name.###'): shell> ls -1 -t -r file_name.[0-9]* | xargs cat | mysql ls is used to get all the log files in the right order. This can be useful if you have to revert to backup files after a crash and you want to redo the updates that occurred between the time of the backup and the crash. You can also use the update logs when you have a mirrored database on another host and you want to replicate the changes that have been made to the master database. See section 20.1 Database replication. 20.4 Running multiple MySQL servers on the same machine There are circumstances when you might want to run multiple servers on the same machine. For example, you might want to test a new MySQL release while leaving your existing production setup undisturbed. Or you might be an Internet service provider that wants to provide independent MySQL installations for different customers. If you want to run multiple servers, the easiest way is to compile the servers with different TCP/IP ports and socket files so they are not both listening to the same TCP/IP port or socket file. Assume an existing server is configured for the default port number and socket file. Then configure the new server with a configure command something like this: shell> ./configure --with-tcp-port=port_number \ --with-unix-socket=file_name \ --prefix=/usr/local/mysql-3.22.9 Here port_number and file_name should be different than the default port number and socket file pathname, and the --prefix value should specify an installation directory different than the one under which the existing MySQL installation is located. You can check the socket and port used by any currently-executing MySQL server with this command: shell> mysqladmin -h hostname --port=port_number variables If you have a MySQL server running on the port you used, you will get a list of some of the most important configurable variables in MySQL, including the socket name. You should also edit the initialization script for your machine (probably `mysql.server') to start and kill multiple mysqld servers. You don't have to recompile a new MySQL server just to start with a different port and socket. You can change the port and socket to be used by specifying them at runtime as options to safe_mysqld: shell> /path/to/safe_mysqld --socket=file_name --port=port_number If you run the new server on the same database directory as another server with logging enabled, you should also specify the name of the log files to safe_mysqld with --log and --log-update. Otherwise, both servers may be trying to write to the same log file. Warning: Normally you should never have two servers that update data in the same database! If your OS doesn't support fault-free system locking, this may lead to unpleasant surprises! If you want to use another database directory for the second server, you can use the --datadir=path option to safe_mysqld. When you want to connect to a MySQL server that is running with a different port than the port that is compiled into your client, you can use one of the following methods: Start the client with --host 'hostname' --port=port_numer or [--host localhost] --socket=file_name. In your C or Perl programs, you can give the port and socket arguments when connecting to the MySQL server. Set the MYSQL_UNIX_PORT and MYSQL_TCP_PORT environment variables to point to the Unix socket and TCP/IP port before you start your clients. If you normally use a specific socket or port, you should place commands to set these environment variables in your `.login' file. See section A Environment variables. See section 13.1 Overview of the different MySQL programs. Specify the default socket and TCP/IP port in the `.my.cnf' file in your home directory. See section 4.15.4 Option files. Go to the first, previous, next, last section, table of contents.