I already wrote about how to move or migrate user accounts from old Linux / UNIX server to a new server including mails and home directories. However, in reality you also need to move MySQL database which may host your blog, forum or just your data stored in MySQL database. The mysqldump command will only export the data and the table structure but it will not include a users grants and privileges. The main function of the MySQL privilege system (which is stored in mysql.user table) is to authenticate a user who connects from a given host and to associate that user with privileges on a database such as SELECT, INSERT, UPDATE, and DELETE.
MySQL Proxy is a simple and new program that sits between your client and MySQL server(s) that can monitor, analyze or transform their communication. Its flexibility allows for a wide variety of use cases, including: a) Load balancing b) Failover c) Query analysis d) Query filtering and modification e) and many more… MySQL Proxy tutorial […]
Usually you run mysqldump to create a database copy and backups as follows:
This is a simple backup solution for people who run their own web server and MySQL database server on a dedicated or VPS server. Most dedicated hosting provider provides backup service using NAS or FTP servers. These service providers will hook you to their redundant centralized storage array over private VLAN. Since, I manage couple of boxes, here is my own automated solution. If you just want a shell script, go here (you just need to provided appropriate input and it will generate FTP backup script for you on fly, you can also grab my php script generator code).
How can I monitor what my MySQL server is doing?
Confused About MySQL Server
If you want to get optimized and speedy response from your MySQL server then you need to add following two configurations directive to your MySQL server: query_cache_size=SIZE The amount of memory (SIZE) allocated for caching query results. The default value is 0, which disables the query cache. query_cache_type=OPTION Set the query cache type. Possible options […]