MySQL DBA common manual

1. MySQL remote connection commands can be used to remotely import and export data
Mysqldump -- default-character-set = gb2312-hsf-0000000.00-uroot-pxxxxxx mydatabase> D: \ data. SQL
Export the specified database to an external SQL file!
Restore command:
MySQL -- default-character-set = gb2312-hsf-0000000.00-uroot-pxxxxxx mydatabase <D: \ data. SQL

2. Three MySQL upgrade Methods
Installation Method: Binary Package compilation and Installation
First: applicable to any storage engine.

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

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

# Mysqldump-p3307-uroot create mysqlsystems_com

3. Back up the database in 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 to the new MySQL database.

# Mysql-p3307-uroot mysqlsystems_com <mysqlsystems_com.bk

5. overwrite all the MySQL databases in the data directory of the old database to the new version.

# Cp-r/opt/mysql-5.1/data/MySQL/opt/mysql-5.4/data (the permission Library remains unchanged)

Note: Everyone knows the importance of this default database.

6. Execute the mysql_upgrade command in the new version. In fact, this command contains the following 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: during each upgrade, we should execute the mysql_upgrade command. It uses the mysqlcheck command to help us check whether the database is compatible with the new version and fix it at the same time, another important role is to use the mysql_fix_privilege_tables command to upgrade the permission table.

7. Disable the old version, change the port used by the new version of the database to 3306, and restart the new version of MySQL database. At this point, the Database Upgrade in a simple environment is over.

Second, it also applies to any storage engine.

1. Install the new MySQL version first.

2. Back up the database in the old version.

# Mkdir/opt/mysqlsystems_bk; mysqldump-p3306-uroot-tab =/opt/mysqlsystems_bk mysqlsystems_com

Note: The-tab option can generate two types of files with the suffix *. SQL and *. txt under the Backup Directory mysqlsystems_bk. The SQL statement stores the SQL statement of the created table and the TXT statement stores the original data.

3. Next, update the data in the new database version.

# 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 step 5, step 6, and step 7 of the first method.

The third method is applicable to the MyISAM storage engine, where all files are copied.

1. install.

2. Copy all. frm,. MYD, And. myi files from the old mysqlsystems_com database to the same directory of the new version.

3. The subsequent steps are the same as those of the first three steps.

The above are three methods to upgrade mysql. It seems that there are no problems. In fact, there are many problems in the actual production environment, this requires us to fully understand what new features are added to the new version before the upgrade, and further analyze whether these new features will affect our original applications after the upgrade.

3. MySQL remote connection failure Solution

It may be that this user has insufficient permissions. View the permission list.

4. MySQL password forgot Solution

In Windows:
Open the command line window and stop MySQL service: net stop MySQL
Install MySQL to start mysql. Use mysqld-nt.exe to start MySQL in the bindirectory. Run mysqld-NT -- skip-grant-tables in the command line window.
Then, open a command line window and execute mysql. You do not need to enter a password.
> Use MySQL
> Update user SET Password = PASSWORD ("new_pass") where user = "root ";
> Flush privileges;
> Exit
Use the task manager to locate the mysqld-nt process and end the process!
After restarting the mysql-NT service, you can log on with a new password.

In Linux:
If MySQL is running, killall-term mysqld is first killed.
Start MYSQL: Bin/safe_mysqld -- skip-grant-tables &
You can access MySQL without a password.
Then
> Use MySQL
> Update user SET Password = PASSWORD ("new_pass") where user = "root ";
> Flush privileges;
Kill MySQL again and start MySql in a normal way.

5. Change the default Character Set of MySQL

Method 1: use the set syntax to specify. If "Global" is not added, it 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: Use the set syntax, which is only valid for this session.
Set names 'utf8 ';

Method 3) directly modify my. CNF, add a line of content, and then restart MySQL to make it take effect globally.
Default-character-set = utf8

6. MySQL slow query analysis tool: mysqldumpslow

Mysqldumpslow command
/Path/mysqldumpslow-s c-T 10/database/MySQL/slow-Log
This will output 10 SQL statements with the maximum number of records, of which:

*-S indicates the sorting method. C, T, L, and R are sorted by the number of records, time, query time, and number of returned records, AC, AT, Al, and Ar indicate reverse descriptions;
*-T indicates the Top N, that is, the number of previous data records returned;
*-G, followed by a regular expression matching mode, which is case insensitive;

For example
/Path/mysqldumpslow-s r-T 10/database/MySQL/slow-Log
You can obtain up to 10 queries from the returned record set.
/Path/mysqldumpslow-s t-T 10-g "left join"/database/MySQL/slow-Log
Obtain the query statements containing the left join in the first 10 results sorted by time.

 

View a MySQL Option
Show variables like '% var_name % ';
Select @ var_name;

When you manage a MySQL database in Linux, there are always some urgent situations. If the database suddenly becomes under great pressure, as a DBA, you may need some common means or commands to analyze the problem and solve it:

When the database suddenly generates pressure, view the SQL statement being queried: (if there is too much content, it indicates that too many SQL statements are executed concurrently, and the database may be congested and slow, under normal circumstances, there should be very few things, that is, connections are all in sleep state)
/Usr/local/MySQL/bin/MySQL-uroot-ppassword databasename-e "show full processlist" | grep-V sleep

There are too many running SQL statements. If you can't see them, you need to sort them and check the SQL statements with the longest execution duration:
/Usr/local/MySQL/bin/MySQL-uroot-ppassword databasename-e "show full processlist" | grep-V sleep | sort-k6rn> sort. tmp

If you find that iowait is very high, check the generation of temporary tables, especially disk TMP table:
/Usr/local/MySQL/bin/MySQL-uroot-ppassword databasename-e "show global status like '% TMP % '"

In this way, you can check what the database is busy with. Which of the busy SQL statements is stuck in? Is it creating a temporary disk file, sending data, and statistics? Solve the problem based on different reasons

---------------------
Frequently used methods for routine management, redo, and Problem Analysis of MySQL replication:

To redo the slave or master changes, you need to synchronize the 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 with caution. The table will be locked based on your DB size, leading to blocking other accesses:
Nohup/usr/local/MySQL/bin/mysqldump-database databasename-uusername-ppassword-lock-all-tables-F> data20070519. SQL &
-F, the master log will be refreshed. In this way, the above change master can be used to synchronize the slave.

Only export the database structure (NO content)
/Usr/local/MySQL/bin/mysqldump-D databasename-uusername-ppassword> data20070519.structure

Only export database data (no statements for creating table structures, etc)
/Usr/local/MySQL/bin/mysqldump-T databasename-uusername-ppassword> data20070519.data

A problem occurs during synchronization (or other problems). view the specific content of BINLOG Based on the location (offset) where the synchronization fails.
/Usr/local/MySQL/bin/mysqlbinlog binlogfilename-start-position = offset
Haha, when we run the SQL statement executed by the master, slave will report a syntax error. It's strange! However, the cause is found: If the bit content is changed to 1 through the stored procedure, this problem will occur, and then the bit will be changed to tinyint (1 ).

Authorize a Server Load balancer instance to have the copy permission:
Grant replication slave on *. * To username @ IP identified by 'Password ';

View the slave status:
Show slave status \ G
View Master Status:
Show Master status;
Reset slave (use with caution)
Reset slave;
If a problem occurs in slave, skip this statement first (make sure that the specific content to be skipped does not affect the subsequent synchronization. Check the BINLOG file using the confirmation method ):
Set global SQL _slave_skip_counter = 1; (remember to pause slave: Stop slave first, and then restart slave: Start slave ;)

----------------
Purely Linux-related:
Tcpdump-a "DST port 3306" to view the communication details of port 3306