Shulou(Shulou.com)05/31 Report--

Editor to share with you what DBA commands are commonly used in MySQL. I hope you will get something after reading this article. Let's discuss it together.

The remote connection command of 1.mysql can import and export data remotely.

Mysqldump-- default-character-set=gb2312-h355.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-h355.255.000.00-uroot-pxxxxxx mydatabase mysqlsystems_com.bk

Note: you can also add the-opt option so that you can export your database in an optimized way 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. 再将旧版本数据库中的data目录下的mysql数据库全部覆盖到新版本中。 # cp -R /opt/mysql-5.1/data/mysql /opt/mysql-5.4/data（权限库保持不变） Note: 大家也都知道这个默认数据库的重要性。 6. 在新版下执行mysql_upgrade命令，其实这个命令包含一下三个命令： # mysqlcheck -check-upgrade -all-databases -auto-repair # mysql_fix_privilege_tables # mysqlcheck -all-databases -check-upgrade -fix-db-names -fix-table-names Note: 在每一次的升级过程中，mysql_upgrade这个命令我们都应该去执行，它通过mysqlcheck命令帮我们去检查表是否兼容新版本的数据库同时 作出修复，还有个很重要的作用就是使用mysql_fix_privilege_tables命令去升级权限表。 7. 关闭旧版本，将新版的数据库的使用端口改为3306，重新启动新版本MySQL数据库。到此，一个简单环境下的数据库升级就结束了。 第二种，同样适用任何存储引擎。 1. 同样先安装好新版本的MySQL。 2. 在旧版本中，备份数据库。 # mkdir /opt/mysqlsystems_bk ; mysqldump -p3306 -uroot -tab=/opt/mysqlsystems_bk mysqlsystems_com Note: -tab选项可以在备份目录mysqlsystems_bk下生成后缀为*.sql和*.txt的两类文件；其中，.sql保存了创建表的SQL语句而.txt保存着原始数据。 3. 接下来在新版本的数据库下更新数据。 # 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. 之后的所有步骤与第一种方法的后三步5、6、7相同。 第三种，适用于MyISAM存储引擎，全部是文件间的拷贝。 1. 安装。 2. 从旧版本mysqlsystems_com数据库下将所有.frm、.MYD 和.MYI文件拷贝到新版本的相同目录下。 3.之后的步骤依然同于第一种的后三步。 以上就是三种升级MySQL的方法，看似没有出现什么问题，其实，在实际的生产环境中，为会有诸多问题发生，这就需要我们在升级之前充分了解新版本中增加了哪些新功能，进一步分析升级以后这些新特性是否将会对我们原来应用产生影响。 3. MYSQL远程连接不了的解决方案 有可能是这个用户权限不够。查看一下权限表。 4. MYSQL忘记密码解决办法 在windows下： 打开命令行窗口，停止mysql服务：Net stop mysql 到mysql的安装路径启动mysql，在bin目录下使用mysqld-nt.exe启动，在命令行窗口执行：mysqld-nt --skip-grant-tables 然后另外打开一个命入令行窗口，执行mysql，此时无需输入密码即可进入。 >

Use mysql

> update user set password=password ("new_pass") where user= "root"

> flush privileges

> exit

Use the Task Manager to find the mysqld-nt process and end the process!

After restarting the mysql-nt service, you can log in with the new password.

Under linux:

If MySQL is running, kill it first: killall-TERM mysqld.

Start MySQL: bin/safe_mysqld-- skip-grant-tables &

You can enter MySQL without a password.

And then there is.

> use mysql

> update user set password=password ("new_pass") where user= "root"

> flush privileges

Kill MySQL again and start MySQL in the normal way.

5. Change the default character set for MYSQL

Law 1. Use SET syntax to specify. Without adding "GLOBAL", it is only valid for this conversation.

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. SET syntax is also used, which is only valid for this session.

SET NAMES 'utf8'

Method 3.) directly modify my.cnf, add one line, 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 outputs the 10 SQL statements with the most records, of which:

*-s indicates how it is sorted. C, t, l and r are sorted by the number of records, time, query time and the number of records returned. Ac, at, al and ar indicate the corresponding flashbacks.

*-t, which means top n, that is, how many pieces of data are returned before

*-g, which can be followed by a regular matching pattern, which is case-insensitive

such as

/ path/mysqldumpslow-s r-t 10 / database/mysql/slow-log

Get the 10 queries that return the most recordsets.

/ path/mysqldumpslow-s t-t 10-g "left join" / database/mysql/slow-log

Get the query statements with left links in the first 10 items sorted by time.

View an option for mysql

Show variables like'% VAR_NAME%'

Select @ @ VAR_NAME

There are always some urgent situations when managing the MySQL database under Linux, and it is found that the database suddenly becomes very stressful, so as a DBA, you may need some common means or commands to analyze where the problem occurs, and then solve it:

Check the SQL being queried when the database is suddenly under pressure: (if there is too much content here means that too many SQL are executed concurrently, maybe the database is blocked and it will be slower and slower. Normally, there should be very few things here, that is, the connections are all in the Sleep state)

/ usr/local/mysql/bin/mysql-uroot-ppassword databaseName-e "show full processlist" | grep-v Sleep

There are too many SQL running to see, so you need to sort them. Look at the 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 that the IOWait is 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 see what the database is busy with, and which step does the busy SQL get stuck on? are you creating temporary disk files, Sending Data, statistics? Solve the problem according to different reasons

-

Common methods for daily management, rework, and problem analysis of Mysql Replication:

To redo Slave, or Master changes, etc., you need to synchronize 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 to SQL text and use it cautiously. The table will be locked according to your DB size, resulting in blocking other accesses:

Nohup / usr/local/mysql/bin/mysqldump-database DATABASEName-uUserName-pPassWord-lock-all-tables-F > DATA20070519.sql &

The Master Log will be refreshed after-F, so that Slave can synchronize with the above Change Master.

Only export the structure of the database (without any content)

/ usr/local/mysql/bin/mysqldump-d DATABASEName-uUserName-pPassWord > DATA20070519.structure

Only export data from the database (no statements to create table structures, etc.)

/ usr/local/mysql/bin/mysqldump-t DATABASEName-uUserName-pPassWord > DATA20070519.data

If there is a problem (or other problem) during synchronization, check the specific content of Binlog according to the location (offset) of the problem.

/ usr/local/mysql/bin/mysqlbinlog binlogFileName-start-position= offset

Ha ha, we have encountered the SQL executed by Master will report syntax errors to Slave, which is weird enough! But that's how we found out the reason: this problem will occur if the content of bit is changed to 1 through a stored procedure, and then change bit to tinyint (1).

Authorize a Slave to have the permission to copy:

Grant replication slave on *. * to username @ IP identified by 'password'

View Slave status:

Show slave status\ G

View Master status:

Show master status

Reset Slave (use with caution)

Reset slave

If there is a problem with Slave, skip this statement first (make sure that the specific content to be skipped will not affect the synchronization later, and check the Binlog file by confirming the method):

Set global sql_slave_skip_counter=1; (remember to pause Slave:stop slave; and restart Slave:start slave;)

After reading this article, I believe you have a certain understanding of "what are the common DBA commands in MySQL". If you want to know more about it, you are welcome to follow the industry information channel. Thank you for reading!

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.