Mysql Backup Basics

  • Filter
  • Time
  • Show
Clear All
new posts

    Mysql Backup Basics

    MySQL is a database server. If you do not know how to backup in MySQL, then MySQL backup may appear to you, complicated or simple, depending on how you got the notion of MySQL backup. MySQL backup basics is actually a simple thing to understand. In this article I introduced you to MySQL backup basics.

    Log Files
    MySQL log files are files that record what the MySQL server is doing. A log file is simply called, "log". I will talk about 3 logs of MySQL in this article: the general query log, the error log and the binary log. The binary log is needed for backup, so I will talk about that, in depth.

    The General Query Log
    The general query log is a general record of what MySQL (server) is doing. The server writes information to this log when clients connect or disconnect, and it logs each SQL statement received from clients. The general query log can be very useful when you suspect an error from a client and want to know exactly what the client sent to the server.

    The server writes statements to the query log in the order that it receives them, which might differ from the order in which they are executed.

    The Error Log
    The error log contains information indicating when the server was started and stopped and also any critical errors that occur while the server is running. If the server notices a table that needs to be automatically checked or repaired, it writes a message to the error log.

    On some operating systems, the error log contains what is known as "stack trace" if the server dies. The stack trace can be used to determine where the server died.

    So the general log records all activities, while the error log records only errors and error related activities.

    The Binary Log
    SQL statements are called events. The binary log contains events that describe database changes such as table creation operations or changes to table data. It also contains events for statements that potentially could have made changes (for example, a DELETE which matched no rows). The binary log has mainly SQL statement events, so that when the server is damaged, you can use it for restoration. Of course the binary log should be saved in a drive that is not part of the computer, which has the MySQL server and data.

    The binary log file has 3 formats, called, Statement-Based Logging, Row-Based Logging and Mixed Logging. The default format is Statement-Based Logging. However, with a few MySQL versions, the default format is Mixed Logging. In this basic tutorial I assume the default for your version is Statement-Based Logging and I will not talk about the formats any further.

    Backup Core
    The core of what is backed up is in a file (saved file). It is not difficult to achieve that. I have not talked much about the file in this article. There is not much to say about it, as you will see. In this article I have talked much about other things associated with backup.

    The secret in MySQL backup, is to understand really what MySQL backup means and to know the commands or SQL statements to use. To that effect, I have prepared a step-by-step tutorial series for you. The series is free. The tutorials are well formatted (typed out) and have no missing special character. Click the following hyperlink to start the series:

    Mysql Backup Basics