Shulou(Shulou.com)06/02 Report--

This article mainly explains "21 common operations sharing in mysql". The content of the explanation is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "21 common operations sharing in mysql".

Create a service script

# cp / usr/local/src/software/mysql-5.1.38/support-files/mysql.server / etc/init.d/mysqld

# chmod 755 / etc/init.d/mysqld

/ / vim / etc/rc.d/init.d/mysqld # datadir=/mysqldata/

# chkconfig-add mysqld

# chkconfig mysqld on

# service mysqld restart

1. Open slow query

Set global long_query_time=2

Set global slow_query_log='ON'

2. Mysql synchronization skips error statements

It is found that the mysql slave server often reports errors in update statements generated by some special characters or symbols, and the whole synchronization will be stuck there. Execute the following three sql statements and skip the error.

Stop slave

Set GLOBAL SQL_SLAVE_SKIP_COUNTER=1

Start slave

3. Mysql ignores specified type error

Add a line under [mysqld] of my.cnf

Slave-skip-errors = 1062 (ignore all 1062 errors)

# slave-skip-errors=1062,1053

# slave-skip-errors=all

4. Error log

Vi / var/log/mysqld.log

5. Replace keywords in a string in bulk

UPDATE BBSTopic SET tcontents = replace (replace (tcontents,'GCD',''), 'kill',') where tcontents like'% GCD%' or tcontents like'% kill%'

6. Random query

During the test, rand () * min (id) is added to select to realize random query. The efficiency of using order by rand () limit 3 will be very low

7. Modify the password

Mysql > set password for root=password ("root")

8. Group to find the maximum value

Select hh.id,hh.name,hh.record from test as hh where hh.record = (select max (record) from test where name=hh.name)

9. Repair and optimize all data

Http://blog.csdn.net/doney_dongxiang/archive/2009/11/26/4878415.aspx

10. Deployment and implementation of MYSQL Server Cluster with load balancing function

Http://www.cnblogs.com/runfeng/archive/2011/04/26/2028924.html

11. View and clean up binlog

Mysql > show binary logs

Mysql > purge binary logs to 'mysqld-bin.000007'

12. API

Mysql_data_seek () / / move the internally returned column pointer to the specified row_number

13. Export the operation record of msyql

Mysql > pager cat > / tmp/t1.txt; mysql > PAGER set to 'cat > / tmp/t1.txt'; mysql > select 1;\! Cat / tmp/t1.txt

14. Get the record line number in mysql

Set @ mycnt = 0There select (@ mycnt:= @ mycnt + 1) as ROWNUM, othercol from tblname order by othercol;select (@ mycnt:= @ mycnt + 1) as ROWNUM, id,name from b, (SELECT @ mycnt:=0) cc;Select UID, (@ rowNum:=@rowNum+1) as rowNo From a, (Select (@ rowNum:= 0)) b Order by a.Money Desc

15. Purge

Mysql > help purgeName: 'PURGE MASTER LOGS'Description:Syntax:PURGE {MASTER | BINARY} LOGS TO' log_name'PURGE {MASTER | BINARY} LOGS BEFORE 'date'Deletes all the binary logs listed in the log index prior to thespecified log or date. The logs also are removed from the list recordedin the log index file, so that the given log becomes the first.This statement has no effect if the-- log-bin option has not beenenabled.URL: http://dev.mysql.com/doc/refman/5.0/en/purge-master-logs.htmlExamples:PURGE MASTER LOGS TO 'mysql-bin.010';PURGE MASTER LOGS BEFORE' 2003-04-02 22 purl 46purl 26'

16. Guide data, separated by specified delimiters

Mysql > select id, name,uid from a_result_user where id > = '20101111' into outfile'/ tmp/a_result_user' FIELDS TERMINATED BY', 'ENCLOSED BY' "'LINES TERMINATED BY'\ nPlease note that if you create a file using the editor in Windows (using\ r\ nas the line Terminator), you should use: mysql > LOAD DATA LOCAL INFILE'/ path/pet.txt' INTO TABLE pet LINES TERMINATED BY'\ r\ n' # if you like, you can clearly indicate the column value delimiter and line end tag in the LOAD DATA statement, but the default tags are locators and newline characters

17. SHOW BINLOG EVENTS

SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]

Used to display events in the binary log. If you do not specify 'log_name', the first binary log is displayed

18. Myisamchk

Myisamchk-- recover * .MYI should be the same as mysql > OPTIMIZE when the table is not updated

Note: you need to have enough spare hard disk space, and remember to mysql > flush tables afterwards.

19. Mysqlcheck

Check, repair, analyze and optimize the table maintenance client program. See Section 8.7, "mysqlcheck: table maintenance and repair procedures".

20. OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name]...

You should use OPTIMIZE TABLE if you have deleted a large portion of the table, or if you have made many changes to tables with variable-length rows (tables with VARCHAR, BLOB, or TEXT columns). Deleted records are kept in the linked list, and subsequent INSERT operations reuse the old record location. You can use OPTIMIZE TABLE to reuse unused space and defragment data files.

In most settings, you don't need to run OPTIMIZE TABLE at all. Even if you make a large number of updates to variable-length rows, you don't need to run them often, once a week or once a month, only on specific tables.

Note that MySQL locks the table while OPTIMIZE TABLE is running. The OPTIMIZE TABLE statement is written to the binary log unless the optional NO_WRITE_TO_BINLOG keyword (or its alias LOCAL) is used. This has been done, so the OPTIMIZE TABLE commands for the MySQL server act like a replication master server, and by default these commands will be replicated to the replication secondary server.

21. Int (M) in the integer data type, M represents the maximum display width

In int (M), the value of M has nothing to do with how much storage space int (M) occupies. Int (3), int (4), int (8) all occupy 4 btyes of storage space on disk. That is, except that the display effect is a little different, int (M) and int data types are the same.

If the value of int is 10:int (10), the result is 0000000010; int (3) shows that the result is 010.

It's just that the length of the display is different, which takes up four bytes of space.

Thank you for reading, the above is the content of "21 common operations sharing in mysql". After the study of this article, I believe you have a deeper understanding of the 21 common operations sharing in mysql, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.