Mysql master/slave database, database/table sharding, and other notes _ MySQL

Mysql master/slave database, database/table sharding, and other notes bitsCN.com

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 the 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

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;

BitsCN.com