MySQL Development---01 a day

July 7, 2015-------------------------

1, TRUNCATE table will empty the value of the auto_increment of the table statement, the ID of a table is the primary key is also self-increment, you can choose to insert the random ID value, assuming not start from 1. When you start insert from 3, and then insert a value that does not have an ID. Self-increment is 4

2. Check the index size of each engine. To optimize database parameters
SELECT? ENGINE,?
ROUND (SUM (data_length)/1024/1024, 1) as "Data MB",?
ROUND (SUM (index_length)/1024/1024, 1) as "index MB",?
ROUND (SUM (data_length + index_length)/1024/1024, 1) as "Total MB",?
COUNT (*) "Num Tables"?
From? Information_schema. TABLES?
WHERE? Table_schema not IN ("Information_schema", "Performance_schema")?
GROUP by? ENGINE;?

3. When preparing a dynamic SQL statement using prepare stmt from. Main
(1) The prepared statement definition must be a session-level variable cannot be a local variable and need to be defined by @. The prepared statement is not lost until the end of the session. Ability to use deallocate prepare stmt to eliminate allocated statements
The table name is indeterminate and the maximum ID of the table is checked. Insert 10 rows of data from id+1
BEGIN
--In a stored procedure, values in general SQL can be variables, but table names, field names cannot be variables
DECLARE v_xname varchar (default ' testincre1 ');
Delete from Test.testincre1 where id=1;
Select Ifnull (max (ID), 0) +1 into @incre from Test.testincre1;
Set @[email protected]+10;
Repeat
? set @sql =concat (' INSERT INTO Test ', v_xname, ' Values (@incre, ' yangsq ', now ());
? select @sql;
? prepare stmt from @sql;
? execute stmt;
? deallocate prepare stmt;
Set @[email protected]+1;
Until @[email protected] end repeat;
END
4, Sql_slave_skip_counter
Last_sql_error:error ' Unknown table ' sakila.testrepldb ' on query. Default database: ' Sakila '. Query: ' DROP TABLE ' testrepldb '/* generated by server */'
mysql> start slave sql_thread; Error: The SQL that caused the errors will be run repeatedly, but Io_thread will still normally accept
2015-07-08 10:42:25 12378 [Warning] Slave sql:if A crash happens this configuration does not guarantee that the relay log Info'll be consistent, error_code:0
2015-07-08 10:42:25 12378 [Note] Slave SQL thread initialized, starting replication in log ' mysql-bin.000012 ' at position 4449, relay log './yaolansvr_slave01-relay-bin.000014 ' position:283
2015-07-08 10:42:25 12378 [ERROR] Slave sql:error ' Unknown table ' sakila.testrepldb ' on query. Default database: ' Sakila '. Query: ' DROP TABLE ' testrepldb '/* generated by Server */', error_code:1051
2015-07-08 10:42:25 12378 [Warning] slave:unknown table ' sakila.testrepldb ' error_code:1051
2015-07-08 10:42:25 12378 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "Slave START". We stopped at log ' mysql-bin.000012 ' position 4449

SELECT @ @sql_slave_skip_counter;
Stop slave;--or stop slave sql_thread
Set global sql_slave_skip_counter=1;
Start slave;

--log-error:
2015-07-08 10:53:30 12378 [Warning] Slave sql:if A crash happens this configuration does not guarantee that the relay log Info'll be consistent, error_code:0
2015-07-08 10:53:30 12378 [Note] Slave SQL thread initialized, starting replication in log ' mysql-bin.000012 ' at position 4449, relay log './yaolansvr_slave01-relay-bin.000014 ' position:283
2015-07-08 10:53:30 12378 [Note] ' sql_slave_skip_counter=1 ' executed at relay_log_file= './yaolansvr_ slave01-relay-bin.000014 ', relay_log_pos= ' 283 ', master_log_name= ' mysql-bin.000012 ', master_log_pos= ' 4449 ' and new Position at relay_log_file= './yaolansvr_slave01-relay-bin.000014 ', relay_log_pos= ' 410 ', master_log_name= ' mysql-bin.000012 ', master_log_pos= ' 4576 '?

5. Query MySQL error from SQL Server errors from data type Dbtype_dbtimestamp to datetime
MySQL a table datetime type data is 0028-01-01 00:00:00. Insert SQL Server datetime error, SQL Server Datime supported date type range is January 1, 1753 to December 31, 9999

6, the relative number of replicate
--replicate-do-table: There is no function like pattern, multiple tables need to be specified multiple times
--replicate-wild-do-table: Example:--replicate-wild-do-table=foo%.bar% replicates only updates with the function of like pattern Use a table where the database name starts with Foo and the table name starts with bar

MySQL Development---01 a day