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

Source: Internet
Author: User
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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.