In general, MySQL is quite fast at restoring data, but I observed that while restoring 20gb of backup its taking more than the usual time. This can happen when you don’t have enough memory or if key_buffer_size is not set high enough, then it can take very long time to re-index the data. In CentOS [...]
Архив тега ‘database’
Speed up large MySQL backup, dump, restore processes (перепечатка)
MySQLTuner mentioned at the O'Reilly MySQL Conference (перепечатка)
If you push play, the video should scoot out to about the 14m40s mark where MySQLTuner appears on one of the slides. Thanks to for letting me know!
is a post from: Major Hayden's blog.
Thanks for following the blog via the RSS feed. Please don't copy my posts or quote portions of them without attribution.
quick web based php script to check replication status of MySQL (перепечатка)
This is a basic PHP script to check replication status of single or multiple MySQL servers. You have to update variables in the script to run in browser like user, password, ip of server etc. Here is the script code: <? ########## # # dbrepstatus.php by Jagbir Singh (jags@jagbir.com) # # ######### ?> <html><head> [...]
Install and configure PhpMyAdmin to manage multiple MySQL Servers (перепечатка)
If you have read and implemented my previous article: how to run multiple MySQL services on a single box, you may like to manage such multiple installation of MySQL or multiple separate MySQL hosts by using a single PhpMyAdmin installation. This is easy enough to do quickly. I assume you have Apache (httpd) and PHP [...]
Monitor MySQL restore progress with pv (перепечатка)
The command is one that I really enjoy using but it's also one that I often forget about. You can't get a much more concise definition of what pv does than this one:
pv allows a user to see the progress of data through a pipeline, by giving information such as time elapsed, percentage completed (with progress bar), current throughput rate, total data transferred, and ETA.
The usage certainly isn't complicated:
To use it, insert it in a pipeline between two processes, with the appropriate options. Its standard input will be passed through to its standard output and progress will be shown on standard error.
A great application of pv is when you're restoring large amounts of data into MySQL, especially if you're restoring data under duress due to an accidentally-dropped table or database. (Who hasn't been there before?) The standard way of restoring data is something we're all familiar with:
# mysql my_database < database_backup.sql
The downside of this method is that you have no idea how quickly your restore is working or when it might be done. You could always open another terminal to monitor the tables and databases as they're created, but that can be hard to follow.
Toss in pv and that problem is solved:# pv database_backup.sql | mysql my_database 96.8MB 0:00:17 [5.51MB/s] [==> ] 11% ETA 0:02:10
When it comes to MySQL, your restore rate is going to be different based on some different factors, so the ETA might not be entirely accurate.
is a post from: Major Hayden's blog.
Beware about admin commands in MySQL replication (перепечатка)
Here is the situation: We are doing replication of only 1 table from a database (live) to other host (backup). so we expect that updates which are on others DBs/tables except on this table should not replicate. Wrong! they got replicated. We shifted one DB say DB1 from live env to Backup DB host and [...]
A simple guide to redundant cloud hosting (перепечатка)
Today, on my 28th birthday, I'm finally delivering on a promise to my readers which I made about two months ago. I've on how to host a web application redundantly in a cloud environment. While it's still a bit of a rough draft, it should be a good starting point for those who haven't worked in virtualized environments before. Also, it may show some of the more experienced systems administrators a new way to do things.
The guide:
As always, if you find anything in the guide that needs improvement, I'm all ears. 
is a post from: Major Hayden's blog.
Compile mysql 5.1 with innodb and optimize for heavy usage (перепечатка)
MySQL stopped default inclusion of InnoDB in latest 5.1.x, so if you need InnoDB, you have to compile it from source. I have done following steps in CentOS 5.4 server to compile MySQL and optimize it later for a heavy site: 1. Remove earlier installation of MySQL, if any and download source rpm from MySQL [...]
How to quickly convert mysql databases from MyISAM to InnoDB (перепечатка)
Recently I was in need to convert all databases of a MySQL server from MyISAM engine to InnoDB. They are facing severe issues due to table level locking in MyISAM and wanted move to InnoDB. This task can be a mammoth work but if you work little smarter, all you need to execute few statements [...]
Find out the clients of your MySQL server (перепечатка)
Sometimes in large deployments, there are cases when MySQL server, setup by you long time back which has been in use by multiple teams in your organization, needs some change or update or intrupption in its service and you are in need to know how many clients are there which connects to this server.
One [...]