MySQL Learning notes

1. Backup command for the MyISAM engine:

Mysqldump--user=root--all-databases--flush-privileges--lock-all-tables--master-data=1--flush-logs--triggers-- Routines--events--hex-blob >/home/data/full_dump.sql

2. Backup command for the InnoDB engine:

Mysqldump--user=root--all-databases--flush-privileges--single-transaction--master-data=1--flush-logs--triggers --routines (

3. Key_buffer_size This parameter is the buffer where the MyISAM engine stores the index.

Mysql> Set global key_buffer_size=8*1024*1024;

Query OK, 0 rows affected (0.01 sec)

4. How to change the parameters of the running database:

Set Global First, then modify the my.cnf for immediate effect and restart to take effect.

5. Mysqlbinlog the Library command:

mysqlbinlog-d db1 mysql-bin.00005 >/data/db1.sql

6. Mysqlbinlog specified starting point split:--result-file=name,-R name

Mysqlbinlog mysql-bin.00005--start-position=550--stop-position=1589-r db1.sql

7. Principle of Master-slave copying:

As a whole, there are 3 steps to replication:

(1) Master changes the record to binary log (these are called binary log events, binary logs event);
(2) Slave copies the binary log events of master to its trunk logs (relay log);
(3) Slave redo the event in the trunk log and change the data to reflect its own.

Describes the process of replication:

650) this.width=650; "Src=" http://hi.csdn.net/attachment/201202/28/0_1330439010 P7li.gif "style=" border:none; "/>

          The first part of the process is the master record binary log. Master records these changes in two logs before each transaction update data is complete. MySQL writes the transaction serially to the binary log, even if the statements in the transaction are cross-executed. After the event is written to the binary log, master notifies the storage engine to commit the transaction.
       The next step is to copy the binary log of master to its own slave. First, slave starts a worker thread--i/o thread. The I/O thread opens a normal connection on master and then starts Binlog dump process. Binlog dump process reads the event from the binary log of master, and if it has been followed by master, it sleeps and waits for master to produce a new event. The I/O thread writes these events to the relay log. The
       SQL slave thread (SQL slave thread) handles the last step of the process. The SQL thread reads events from the log and replays the events in them to update the slave data so that it is consistent with the data in master. As long as the thread is consistent with the I/O thread, the trunk log is typically located in the OS cache, so the overhead of the trunk log is minimal.
        In addition, there is a worker thread in master: As with other MySQL connections, Slave opening a connection in master also causes Master to start a thread. The replication process has a very important limitation-replication is serialized on slave, meaning that parallel update operations on Master cannot operate concurrently on slave.

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/76/39/wKioL1ZNccPTxe4fAAD4CSs-C50280.jpg "title=" MySQl Replication copy principle. jpg "alt=" wkiol1znccptxe4faad4css-c50280.jpg "/>

MySQL Learning notes