MySQL Master/Slave database, database/table sharding, and other notes

 

1. MySQL Directory:/var/lib/MySQL default directory:/usr/local/MySQL during RPM or yum Installation

2. MySQL installed in the RPM package will not install the/etc/My. CNF file,
As to why MySQL can be started and used normally without this file, there are two points,
The first statement is that my. CNF is only a parameter file during MySQL startup, but it does not exist. In this case, MySQL will start with the built-in default parameter,
Second, MySQL automatically uses the my-medium.cnf file under the/usr/share/MySQL directory at startup, which is limited to MySQL installed in RPM packages,
Solution: copy the. CNF file under the/usr/share/MySQL directory to the/etc directory and change it to my. CNF.

The/usr/share/MySQL directory contains many language directories and multiple configuration files for different types of projects.

3. Configure MySQL Character Set settings:

In the configuration file

Add default-character-set = utf8 after socket =/var/lib/MySQL. Sock (determines the client Character Set and connection Character Set)

After myisam_sort_buffer_size = 8 m, add character-set-Server = utf8 (determines the server Character Set and the [database, table, field] Character Set of the database, due to the inheritance relationship ), add collaction-Server = usf8_general_ci)

4. Enable MySQL Log-bin:

Enable log-bin = mysql-bin in the configuration file (equivalent to the following content. mysql-bin only indicates that the log-bin content is written into mysql-bin ), after restarting MySQL, The mysql-bin.000001 and mysql-bin.index can be found under/var/lib/MySQL, indicating that mysql-bin has been enabled

5. Some commands about mysqlbin

View the bin-log at a MySQL prompt
Show Master status;
# Display the last bin-log and its last location
Show Master logs;
# View all bin-log logs and mark the last POS position

6. Use the mysqlbinlog command to view BINLOG

If a character set error is prompted --- mysqlbinlog: Unknown variable 'default-character-set = utf8', you can use mysqlbinlog -- no-defaults/var/lib/MySQL/mysql-bin.000001 to ignore Character Set errors

7. When will a new bin-log be generated?
1)Restart MySQL Service
2)Flush logs;
# Refresh the bin-log, will generate a new bin-log such as: mysql-bin.00008
3)Reset master;
# Clear all the logs in the bin-log, and finally only the mysql-bin.00001 is left and the location is the initial location

Requirement: real data backup and recovery within the company. Back up data at and refresh the bin-log at the same time, but the data is damaged at. How can we restore all data since? (In the compilation and installation of MySQL environment)

1.9: 30 backup data:
/Usr/local/MySQL/bin/mysqldump-uroot-p123 test-l-f>/tmp/test. SQL
-L lock the table (to prevent users from modifying or adding data during the table import process), read-only-F (flush) refresh the bin-log (generate a new log file and save the subsequent log records)
2.9: 30-data will be written to bin-Log
/Usr/loca/MySQL/bin/mysqlbinlog -- no-defaults mysql-bin.000003
# Only add, delete, and modify statements are saved.
3.10: 00 data corruption, how to restore data:
1) restore the data backed up
/Usr/local/MySQL/bin/MySQL-Uroot-p123 test </tmp/test. SQL
2) restore the add, delete, and modify statements in the bin-log between and.
/Usr/local/MySQL/bin/mysqlbinlog -- no-defaults -- stop-position = 367 mysql-bin.000003 |/usr/local/MySQL/bin/MySQL-uroot-p123 Test

-- Stop-position: the last log of the logbin to be restored. You can query the location of the log file, and -- start-position indicates that the data after the location is restored.
4. In this way, all data is restored.

8. Master/Slave Databases

Slave MySQL Server:
1. VI/etc/My. CNF
Server-id = 2
Log-bin = mysql-bin
Master-host = 192.168.255.1
Master-user = user1
Masters-Password = 123
Master-Port = 3306
2. Restart MySQL
3. Show slave status \ G
Slave_io_running: Yes
# Bin-log copied from the master server
Slave_ SQL _running: Yes
# Successfully executed the copied SQL statement

4. Error
# Cause: the connection to the last POS location of the master server fails.
Slave stop;
Change master
Master_host = "192.168.10.1 ",
Master_user = "user1 ",
Master_password = '000000 ',
Master_port = 3306,
# Show Master Status on the master server;
Master_log_file = "mysql-bin.000001 ",
Master_log_pos = 279;
Slave start;