Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

Chapter 19 simple MySQL operation

2025-04-02 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Share

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

1. How to change the system environment variable PATH?

Vim / etc/profile.d/path.sh join

#! / bin/bash

Export PATH=$PATH:/usr/local/mysql/bin:/usr/local/apache2/bin

two。 After the default mysql is installed, there is no root password, how to set a password for root?

Mysqladmin-uroot password 'newpass'

3. How do I change my root password?

Mysqladmin-uroot-poldpasswd password 'newpasswd'

4. How do I connect to a remote mysql server?

Mysql-uusername-ppass-hhostip-Pport

Username username pass password hostip the port of the ip-P connection of another machine

5. How to check the account currently logged into mysql?

Select user ()

6. How do I switch a library under the mysql command line?

Use databasename; or use mysql; or use discuz

7. How do I see what fields a table has?

Desc tablename

8. How do I see which database engine is used for a table?

Show create table\ G

9. How do I see what queues there are in the current database?

Show processlist

10. How can I see what slow queries there are when there are many queues?

Slow query log, slow query log is set in / etc/my.cnf by adding:

Log_slow_queries = / data/mysql/slow.log

Long_query_time = 1 / / log will be recorded if the query time exceeds 1s

View the log file cat / data/mysql/slow.log

Command show global status like "slow%"

11. How do I view the parameter values of the current mysql?

Show variables

twelve。 How to change a parameter without restarting the mysql service?

Set global xxx = xxx;, for example

Set global wait_timeout = 10; or set global max_connect_errors=1000

13. What tool do you use to back up the database? Distinguish between the backup of the myisam engine and the innodb engine.

Mysqldump backs up the database, and mysqldump can back up data from both engines. But innodb engine data is faster using xtrabackup tools.

14. Briefly describe the difference between myisam and innodb engines.

Innodb does not support fulltext type indexes

Innodb does not save the number of rows of the table

Myisam's data is stored directly in the system file, while innodb's database creates a data tablespace file in advance, and then stores the data from this tablespace file

Myisam does not provide transaction support, while InnoDB provides transaction support, foreign keys and other advanced database functions.

Myisam's lock is locked on the entire table, and innodb is a row lock.

15. If your mysql service doesn't start and the current terminal doesn't report an error, what do you do?

Check the log of mysql. The log defaults to the .err file with the name of hostname under datadir.

16. The character set of the library to be backed up is the character set of gbk. In order to avoid garbled code, how to specify the character set as gbk when backing up and restoring?

Backup specified mysqldump-- default-character-set=gbk

Restore the specified mysql-- default-character-set=gbk

17. In the error log, if there is a prompt that a table is damaged and needs to be repaired, how do you fix the table?

Repair table tablename; such as mysql > repair table discuz.pre_forum_post

18. When backing up the database of the myisam engine, in addition to using the mysqldump tool to back up, we can also directly copy the source data of the database (.frm, .MYD, .MYI data). Which file can not be copied? What do I do if I want to recover the file?

The file of .MYI can not be copied. When restoring, you need to repair the table, but add use_frm, such as

Repair table tb1 use_frm

19. What if I forget my root password for mysql?

1) Edit mysql main configuration file my.cnf vim / etc/my.cnf

Add the parameter skip-grant under the [mysqld] field

2) restart the database service service mysqld restart

3) this allows access to the database without authorization of mysql-uroot

4) modify the corresponding user password use mysql

Update user set password=password ('your password') where user='root'

Flush privileges

5) modify / etc/my.cnf to remove skip-grant and restart the mysql service

20. How to change the password of mysql's ordinary account?

Update user set password=password ('your password') where user='username'

21. Where is the command history file of mysql? for the sake of security, we can actually do a little processing to keep the command history of mysql from being recorded in the document. Please think about how to use the knowledge we have learned to do it.

~ / .mysql_history

We can do this without saving the history of mysql commands: cd ~; rm-f. MySQL _ history; ln-s / dev/null. MySQL _ history

twenty-two。 How do I make the listening port of mysql 3307 instead of the default 3306?

Vim / etc/my.cnf

Change port = 3306 to port = 3307

Extended reading:

Detailed explanation of mysql configuration parameters http://www.lishiming.net/thread-87-1-1.html

The difference between mysql 5.0and 5.1logging slow query logs http://www.lishiming.net/thread-374-1-1.html

Myisamchk repair table http://www.lishiming.net/thread-256-1-1.html

Start multiple port http://www.lishiming.net/thread-63-1-1.html on the same MySQL server

Mysqld_multi stop can't stop mysql http://www.lishiming.net/thread-624-1-1.html.

Mysql uses the innodb engine http://www.lishiming.net/thread-955-1-1.html

The mysql innodb engine allocates a tablespace http://www.lishiming.net/thread-5498-1-1.html for each table

How to view the command history of mysql http://www.lishiming.net/thread-1022-1-1.html

Mysql-5.1 does not support innodb engine and gbk character set http://www.lishiming.net/thread-1229-1-1.html by default.

Mysql starts port http://www.lishiming.net/thread-208-1-1.html on the specified IP

What if mysql forgets the root password, http://www.lishiming.net/thread-252-1-1.html?

Windows mysql root password forgot how to do http://www.lishiming.net/thread-1014-1-1.html

Mysql deletes the first 100 pieces of data http://www.lishiming.net/thread-5452-1-1.html of a table

Mysql commonly licensed http://www.lishiming.net/thread-88-1-1.html

Mysqldump backup and restore specified table http://www.lishiming.net/thread-131-1-1.html

Mysql version 5.0 take 5.1 modifies the password of an ordinary user http://www.lishiming.net/thread-892-1-1.html

Mysql displays SQL statement execution time http://www.lishiming.net/thread-214-1-1.html

Mysql copies a table and copies the sql statement http://www.lishiming.net/thread-219-1-1.html of a table structure

The mysql innodb engine allocates a tablespace http://www.lishiming.net/thread-5498-1-1.html for each table

Using xtrabackup to back up the database http://www.lishiming.net/thread-956-1-1.html of the innodb engine

Several storage engines http://www.lishiming.net/thread-5501-1-1.html supported by mysql5.1

When mysql imports a very large sql file, the mysql service restarts http://www.lishiming.net/thread-5395-1-1.html

Comparison of mysql myisam and innodb engines with http://www.lishiming.net/thread-97-1-1.html

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.

Views: 0

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.

Share To

Database

Wechat

© 2024 shulou.com SLNews company. All rights reserved.

12
Report