Steps:
1. Stop your mysql server before starting this operation
2. Create the directories that will be new datadir
3. chown the directory to the mysql:mysql user
4. copy the files from the old datadir to the new location (cp -p : preserves ownership). However, make sure that the files
named ib_arch_log_0000000000, ib_logfile0 etc. are not copied to the newer location
5. Make sure that the files and directories are owned by mysql user
6. Make changes in the my.cnf to point the new datadir (datadir = /my/new/dir/)
7. Restart the MySQL database
8. Create a new database and verify that the files for this database are getting created in the new datadir (create database test)
9. After the server is running for a few days properly, get rid of the old data.mysql -D DATABASE -u USERNAME -p -h HOST --default-character-set=utf8 < FILE.sql
1) mysqladmin extended (absolute values) The values making most sense to monitor are: * Slave_running: If the system is a slave replication server, this is an indication of the slave's health. * Threads_connected: The number of clients currrently connected. This should be less than some preset value (like 200), but you can also monitor that it is larger than some value to ensure that clients are active. * Threads_running: If the database is overloaded you'll get an increased number of queries running. That also should be less than some preset value (20?). It is OK to have values over the limit for very short times. Then you can monitor some other values, when the Threads_running was more than the preset value and when it did not fall back in 5 seconds. 2) mysqladmin extended (counters) The idea is that you store the performance counter value and compute the difference with the new values. The interval between the recordings should be more than 10 seconds. The following values are good candidates for checking: * Aborted_clients: The number of clients that were aborted (because they did not properly close the connection to the MySQL server). For some applications this can be OK, but for some other applications you might want to track the value, as aborted connects may indicate some sort of application failure. * Questions: Number of queries you get per second. Also, it's total queries, not number per second. To get number per second, you must divide Questions by Uptime. * Handler_*: If you want to monitor low-level database load, these are good values to track. If the value of Handler_read_rnd_next is abnormal relative to the value that you normally would expect, it may indicate some optimization or index problems. Handler_rollback will show the number of queries that have been rolled back. You might want to wish to investigate them. * Opened_tables: Number of table cache misses. If the value is large, you probably need to increase table_cache. Typically you would want this to be less than 1 or 2 opened tables per second. * Select_full_join: Joins performed without keys. This should be zero. This is a good way to catch development errors, as just a few such queries can degrease the system's performance. * Select_scan: Number of queries that performed a full table scan. In some cases these are OK but their ratio to all queries should be constant. if you have the value growing it can be a problem with the optimizer, lack of indexes or some other problem * Slow_queries: Number of queries longer than --long-query-time or that are not using indexes. These should be a small fraction of all queries. If it grows, the system will have performance problems. * Threads_created: This should be low. Higher values may mean that you need to increase the value of thread_cache or you have the amount of connections increasing, which also indicates a potential problem.
Lock a database: $ mysql -u user -p mysql> FLUSH TABLES WITH READ LOCK; This closes all open tables and locks all tables for all databases with a read lock until you execute UNLOCK TABLES. Unlock: mysql> UNLOCK TABLES; (to lock a single table: LOCK TABLES table READ;)
First / Previous / Next / Last / Page 1 of 3 (27 posteets)
