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

Mysql Master/Slave database, database/table sharding, and other notes 1. mysql Directory:/var/lib/mysql default directory during rpm or yum installation: /usr/local/mysql 2. MySQL installed with the rpm package will not install/etc/my. the cnf file, as to why MySQL can still start and work normally without this file, there are two statements at the point. The first one is my. cnf is only a parameter file during MySQL startup. It can be left empty. MySQL will start with the built-in default parameter. The second statement is as follows, mySQL automatically uses the my-medium.cnf file under the/usr/share/mysql directory at startup, this statement is limited to MySQL installed in the rpm package, solution, you only need to copy one/usr/share/mysql directory. cnf file to the/etc directory and renamed it my. cnf. In the/usr/share/mysql directory, there are many language directories and multiple mysql configuration files for different types of projects. 3. Configure mysql Character Set settings: in the configuration file, specify socket =/var/lib/mysql. add default-character-set = utf8 after sock (determines the character set of customer service and connection) 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 (this is to set the verification Character Set and act on order by) 4. Enable mysql log-bin: enable log-bin = mysql-bin in the configuration file. (if it is equal to the following content, you can write it at will. mysql-bin only indicates log-bin. The content written into mysql-bin), restart mysql, in/var/lib/mysql can find the mysql-bin.000001 and mysql-bin.index, note that mysql-bin has been enabled. 5. view the bin-log show master status at the mysql prompt for some commands about mysqlbin; # display the last bin-log and its last location show master logs; # view all bin-log logs, mark the last pos position at the same time. 6. Run the mysqlbinlog command to view the binlog. If a character set error is prompted, use mysqlbinlog: unknown variable 'default-character-set = utf8 ', you can ignore Character Set errors with mysqlbinlog -- no-defaults/var/lib/mysql/mysql-bin.000001 7. When will a new bin-log be generated? 1) Restart mysql service. 2) flush logs; # refresh the bin-log. A new bin-log will be generated, for example: mysql-bin.00008 3) reset master; # Clear all in the bin-log, and finally only the mysql-bin.00001 and the position for the initial position of the demand: the company's real data backup and recovery, at backup data, at the same time, the bin-log is refreshed, but the data is corrupted at. How can we restore all the 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 locks the table (to prevent users from modifying or adding data during table export), 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 save add, delete, modify statement 3.10: 00 data corruption, how to recover data: 1) restore data backed up at/usr/local/mysql/bin/mysql-uroot-p123 test </tmp/test. SQL 2) restore add, delete, modify statement/usr/local/mysql/bin/mysqlbinlog -- no-defaults -- stop-position = 367 mysql-bin.000003 in the bin-log between-|/ usr/local/mysql/bin/mysql-uroot-p123 test -- stop-position indicates the last log to restore Data in logbin, you can query the location of a log file, and -- start-position indicates that data from this location is restored. 4. in this way, all data is restored 8. Master/Slave databases are from the mysql server: 1.vi/etc/my. cnf server-id = 2 log-bin = mysql-bin master-host = 192.168.255.1 master-user = user1 master-password = 123 master-port = 3306 2. restart mysql 3. show slave status \ G Slave_IO_Running: Yes # The bin-log is successfully copied from the master server. Slave_ SQL _Running: Yes # The copied SQL statement is successfully executed. 4. error # cause: the connection to the last pos of the master server fails. slave stop; change master tomaster_host = "192.168.10.1", master_user = "user1", master_password = '2016 ', master_port = 3306, # show master status on the master server; master_log_file = "mysql-bin.000001", master_log_pos = 279; slave start;