MySQL DBA Common Manual Summary _mysql

1.mysql Remote Connection command can import exported data remotely
Mysqldump--default-character-set=gb2312-h255.255.000.00-uroot-pxxxxxx Mydatabase>d:\data.sql
Export the specified database to an external SQL file!
Restore command:
MySQL--default-character-set=gb2312-h255.255.000.00-uroot-pxxxxxx mydatabase<d:\data.sql

2. mysql Three upgrade methods
Installation Method: Binary Package compilation installation
First: For any kind of storage engine.

1. Download and install the new version of the MySQL database, and change its port to 3307 (avoid the previous version of the 3306 conflict), start the service.

2. Create a database with the same name under a new version.

# Mysqldump-p3307-uroot Create mysqlsystems_com

3. Back up the database under the old version.

# mysqldump-p3306-uroot mysqlsystems_com > MYSQLSYSTEMS_COM.BK

Note: You can also add the –opt option so that you can use the optimization method to export your database to reduce unknown problems.

4. Import the exported database backup into the new version of the MySQL database.

# Mysql-p3307-uroot Mysqlsystems_com < MYSQLSYSTEMS_COM.BK

5. Overwrite the MySQL database in the data directory of the old version database to the new version.

# cp-r/opt/mysql-5.1/data/mysql/opt/mysql-5.4/data (permission library remains unchanged)

Note: We all know the importance of this default database.

6. In the new version of the implementation of the Mysql_upgrade command, in fact, this command contains three commands:

# Mysqlcheck–check-upgrade–all-databases–auto-repair
# Mysql_fix_privilege_tables
# Mysqlcheck–all-databases–check-upgrade–fix-db-names–fix-table-names

Note: In each upgrade process, Mysql_upgrade This command we should all do, it through the Mysqlcheck command to help us check whether the table is compatible with the new version of the database and fix, there is also a very important role is to use the Mysql_fix_ Privilege_tables command to upgrade the permission table.

7. Turn off the old version, change the new version of the database using the port to 3306 and restart the MySQL database. In this case, the database upgrade in a simple environment is over.

The second, the same applies to any storage engine.

1. Also install the new version of MySQL first.

2. In the old version, back up the database.

# MKDIR/OPT/MYSQLSYSTEMS_BK; MYSQLDUMP-P3306-UROOT–TAB=/OPT/MYSQLSYSTEMS_BK mysqlsystems_com

The Note:–tab option can generate two types of files suffix *.sql and *.txt under the backup directory MYSQLSYSTEMS_BK, where. SQL saves the SQL statement that created the table and. TXT preserves the original data.

3. Next, update the data under the new version of the database.

# Mysqladmin-p3307-uroot Create mysqlsystems_com

# Cat/opt/mysqlsystems_bk/*.sql | Mysql-p3307-uroot mysqlsystems_com (Create Tables)

# Mysqlimport Mysqlsystems_com/opt/mysqlsystems_bk/*.txt (Load Data)

4. All subsequent steps are the same as those in the first three steps 5, 6, and 7.

The third is for MyISAM storage engines, all of which are copies between files.

1. Installation.

2. From the old version of the mysqlsystems_com database, all. frm,. MyD and. myi files are copied to the same directory as the new version.

3. The subsequent steps remain the same as in the first three stages.

The above are three ways to upgrade MySQL, there seems to be no problem, in fact, in the actual production environment, there will be a lot of problems, which requires us to fully understand the new version of new features before the upgrade, further analysis of the upgrade after the new features will affect our original application.

3. mysql Remote connection solution

It is possible that this user has insufficient permissions. Check the permission table.

4. mysql Forgot password solution

Under Windows:
Open a command line window to stop the MySQL service: Net stop MySQL
To MySQL installation path to start MySQL, in the bin directory using Mysqld-nt.exe startup, in the Command line window execution: mysqld-nt--skip-grant-tables
Then open a separate command line window, execute MySQL, at this time without entering a password to enter.
>use MySQL
>update User Set Password=password ("New_pass") where user= "root";
>flush privileges;
>exit
Using Task Manager, find the MYSQLD-NT process and end the process!
When you restart the Mysql-nt service, you can log in with the new password.

Under Linux:
If MySQL is running, first kill: Killall-term mysqld.
Start Mysql:bin/safe_mysqld--skip-grant-tables &
You can enter MySQL without the need for a password.
Then it's
>use MySQL
>update User Set Password=password ("New_pass") where user= "root";
>flush privileges;
Re-kill MySQL, the normal way to start MySQL.

5. Change the default character set for MySQL

Method 1, with the SET syntax to specify, without the word "GLOBAL" is only valid for this session
SET [GLOBAL] character_set_client = UTF8;
SET [GLOBAL] character_set_connection = UTF8;
SET [GLOBAL] character_set_database = UTF8;
SET [GLOBAL] character_set_results = UTF8;
SET [GLOBAL] character_set_server = UTF8;

Method 2, also with set syntax, is only valid for this session
SET NAMES ' UTF8 ';

Method 3, directly modify MY.CNF, add a line of content, and then restart MySQL, so that the global effective
Default-character-set = UTF8

6.MYSQL Slow Query analysis tool: Mysqldumpslow

Mysqldumpslow command
/path/mysqldumpslow-s c-t 10/database/mysql/slow-log
This outputs the 10 SQL statements that have the highest number of records:

*-S, is to indicate the way in which the C, T, L, R is sorted according to the number of records, time, query time, the number of records returned, AC, at, AL, AR, indicating the corresponding flashback;
*-T, is the meaning of top N, that is, to return the number of previous data;
*-G, you can write a regular matching mode, the case is not sensitive;

Like what
/path/mysqldumpslow-s r-t 10/database/mysql/slow-log
Gets the 10 queries that return the recordset the most.
/path/mysqldumpslow-s t-t 10-g "left join"/database/mysql/slow-log
Get the first 10 items in chronological order that contain the query statement with the left connection.



View one of the options for MySQL
Show variables like '%var_name% ';
SELECT @ @VAR_NAME;


When managing a MySQL database under Linux, there are always some very urgent situations, and the database suddenly becomes very stressful, so as a DBA, you may need some common tools or commands to analyze where the problem occurs, and then resolve:

To view the SQL that is being queried when the database is suddenly stressed: (if there is too much content to execute concurrently, perhaps the database is blocked, it will become slower, and normally there should be very few things, that is, the connection is in sleep state)
/usr/local/mysql/bin/mysql-uroot-ppassword databasename-e "show full processlist" | Grep-v Sleep

There are too many running SQL to see, that needs to be sorted to see those SQL with the longest execution time:
/usr/local/mysql/bin/mysql-uroot-ppassword databasename-e "show full processlist" | Grep-v Sleep | Sort-k6rn >sort.tmp

If you find iowait very high, look at the generation of temporary tables, especially disk TMP table:
/usr/local/mysql/bin/mysql-uroot-ppassword databasename-e "show global status Like '%tmp% '"

By doing so, you can see what the database is busy with, and where are those busy SQL stuck in the process of creating disk temp files, sending Data, statistics? To solve a problem according to different reasons

—————————————————————
About MySQL replication day-to-day management, redo, problem analysis commonly used methods:

Redo slave, or master changes, and so on, need to sync slave with the new master:
Change Master to master_host=ip,master_user= ' replication UserName ', master
_password= ' Replication Passwrod ', master_log_file= ' log-bin.000001′,master_log_pos=0;

Export data into SQL text, use caution, depending on your db size will lock the table, resulting in blocking other access:
Nohup/usr/local/mysql/bin/mysqldump–database databasename-uusername-ppassword–lock-all-tables-f > Data20070519.sql &
The master log will be refreshed after-F to match the change master above to allow slave to sync

Export only the structure of the database (without any content)
/usr/local/mysql/bin/mysqldump-d Databasename-uusername-ppassword >data20070519.structure

Export only database data (statements that do not create a table structure, and so on)
/usr/local/mysql/bin/mysqldump-t Databasename-uusername-ppassword >data20070519.data

There is a problem (or some other problem) with the synchronization, depending on the location (offset) Where the problem occurs, view the details of the Binlog
/usr/local/mysql/bin/mysqlbinlog binlogfilename–start-position= Offset
Oh, we ran into the master executed SQL to the slave will report grammatical errors, enough strange it! But that's the reason: if you change the bit content to 1 with the stored procedure, you'll see the problem, and then change the bit to tinyint (1).

Authorize a Slave to have replication permissions:
Grant replication slave on *.* to user name @ip identified by ' password ';

To view the slave status:
Show slave status \g
View Master Status:
Show master status;
Reset slave (with caution)
Reset slave;
Slave there is a problem, skip this statement first (please confirm that the specific content you want to skip will not affect subsequent synchronizations, confirm the method to view the Binlog file):
Set global sql_slave_skip_counter=1; (Remember to pause slave:stop slave; then restart Slave:start slave;)

———————————————–
Purely Linux-Related:
Tcpdump-a "DST Port 3306″ View 3306-Port traffic specific content