How to change the MySQL root user password 01/15 Update SLTechnology News&Howtos

How to change the MySQL root user password

2025-01-15 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

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

This article mainly explains "how to change the MySQL root user password", the content of the article is simple and clear, easy to learn and understand, now please follow the editor's ideas slowly in depth, together to study and learn "how to change the MySQL root user password" bar!

1. Some basic concepts

1) the MySQL architecture consists of five main subsystems: query engine, storage manager, buffer manager, transaction manager and recovery manager.

2) the query engine consists of three related components: the parser, the query optimizer and the execution component.

3) in addition to the five primary subsystems, the MySQL architecture also includes two auxiliary components: process manager and function library.

4) in MySQL, the beginning of the transaction is marked as a BEGIN statement (unlike Oracle).

two。 Modify the MySQL root user password after installation is complete

C:Documents and SettingsAdministrator > mysql-u root

Welcome to the MySQL monitor. Commands end with; or g.

Your MySQL connection id is 18

Server version: 5.1.34-community MySQL Community Server (GPL)

Type 'help;' or' h' for help. Type 'c'to clear the current input statement.

Mysql > use mysql

Database changed

Mysql > set password for 'root'@'localhost' = password (' passwd')

Query OK, 0 rows affected (0.00 sec)

Mysql > quit

Bye

[@ more@] C:Documents and SettingsAdministrator > mysql-u root

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: n

C:Documents and SettingsAdministrator > mysql-u root-p

Enter password: *

Welcome to the MySQL monitor. Commands end with; or g.

Your MySQL connection id is 20

Server version: 5.1.34-community MySQL Community Server (GPL)

Type 'help;' or' h' for help. Type 'c'to clear the current input statement.

3. Modify the storage path of data files

Shut down the MySQL server first:

C:Documents and SettingsAdministrator > mysqladmin-u root-p shutdown

Enter password: *

Modify the datadir parameter in the my.ini configuration file (placed under D:Program FilesMySQLMySQL Server 5.1 by default):

# Path to the database root

Datadir= "D:MySQL Datafilesdata"

Start the MySQL server by starting the system service.

4. A series of simple operations

1) create the database, view the database, and select the database you want to use

Mysql > create database ggyy

Query OK, 1 row affected (0.43 sec)

Mysql > show databases

+-+

| | Database |

+-+

| | information_schema |

| | ggyy |

| | mysql |

| | test |

+-+

4 rows in set (0.00 sec)

Mysql > show databases

+-+

| | Database |

+-+

| | information_schema |

| | ggyy |

| | mysql |

| | test |

+-+

4 rows in set (0.00 sec)

Mysql > use ggyy

Database changed

2) create tables and view tables

Mysql > create table members

-> (

-> id int (3) auto_increment

-> fname varchar (20) not null

-> lname varchar (20) not null

Tel varchar (15)

> email varchar (50)

-> primary key (id)

->)

Query OK, 0 rows affected (0.49 sec)

Mysql > show tables

+-+

| | Tables_in_ggyy |

+-+

| | members |

+-+

1 row in set (0.01 sec)

Note: the auto_increment modifier applies only to integer fields, indicating that MySQL will automatically generate a number for this field (by adding 1 to the previous value). A table can have only one auto_increment field, and this field must be defined as a key (that is, there must be an index on the field, and the terms "key" and "index" are equivalent in MySQL).

At this point, you can see the newly generated file under the path where the data file is stored:

D:MySQL Datafilesdataggyy > dir

Volume in drive D is Data

Volume Serial Number is D632-9209

Directory of D:MySQL Datafilesdataggyy

2009-05-18 10:58

2009-05-18 10 Rd 58.

2009-05-18 10:18 65 db.opt

2009-05-18 10:36 8680 members.frm

2 File (s) 8745 bytes

2 Dir (s) 66038996992 bytes free

3) add columns, modify columns, and delete columns

Mysql > desc members

+-+ +

+-+ +

| | id | int (3) | NO | PRI | NULL | auto_increment |

| | tel | varchar (15) | YES | | NULL |

+-+ +

5 rows in set (0.01 sec)

Mysql > alter table members add remark varchar (50)

Query OK, 0 rows affected (0.67 sec)

Records: 0 Duplicates: 0 Warnings: 0

Mysql > desc members

+-+ +

+-+ +

| | id | int (3) | NO | PRI | NULL | auto_increment |

| | tel | varchar (15) | YES | | NULL |

+-+ +

6 rows in set (0.04 sec)

Mysql > alter table members modify remark varchar

Query OK, 0 rows affected (0.18 sec)

Records: 0 Duplicates: 0 Warnings: 0

Mysql > desc members

+-+ +

+-+ +

| | id | int (3) | NO | PRI | NULL | auto_increment |

| | tel | varchar (15) | YES | | NULL |

+-+ +

6 rows in set (0.01 sec)

Mysql > alter table members drop remark

Query OK, 0 rows affected (0.18 sec)

Records: 0 Duplicates: 0 Warnings: 0

Mysql > desc members

+-+ +

+-+ +

| | id | int (3) | NO | PRI | NULL | auto_increment |

| | tel | varchar (15) | YES | | NULL |

+-+ +

5 rows in set (0.01 sec)

4) insert records, query tables, modify records, delete records

Mysql > insert into members (id, fname, lname, tel, email) values (1, 'Yue',' Gao

',' 1234567, 'yuegao@company.com')

Query OK, 1 row affected (0.07 sec)

Mysql > insert into members values (3, 'Feng',' Song', '7654321,' fengsong@compa

Ny.com')

Query OK, 1 row affected (0.43 sec)

Mysql > select * from members

+-+-

+-+-

| | 1 | Yue | Gao | 1234567 | yuegao@company.com |

+-+-

2 rows in set (0.00 sec)

Mysql > select * from members

+-+-

+-+-

| | 1 | Yue | Gao | 1234567 | yuegao@company.com |

+-+-

2 rows in set (0.01sec)

Mysql > insert into members (fname, lname, email) values ('Chen',' Chu', 'chenchu

@ company.com')

Query OK, 1 row affected (0.44 sec)

Mysql > select * from members

+-+-

+-+-

| | 1 | Yue | Gao | 1234567 | yuegao@company.com |

+-+-

3 rows in set (0.00 sec)

Here you can see the role of the auto_increment modifier, which automatically sets the value of the newly inserted record id segment to 4 instead of 2.

Mysql > update members set id = 2 where id = 3

Query OK, 1 row affected (0.05sec)

Rows matched: 1 Changed: 1 Warnings: 0

Mysql > select * from members

+-+-

+-+-

| | 1 | Yue | Gao | 1234567 | yuegao@company.com |

+-+-

3 rows in set (0.00 sec)

Mysql > delete from members where id = 4

Query OK, 1 row affected (0.43 sec)

Mysql > select * from members

+-+-

+-+-

| | 1 | Yue | Gao | 1234567 | yuegao@company.com |

+-+-

2 rows in set (0.00 sec)

Mysql > insert into members (fname, lname, email) values ('Chen',' Chu', 'chenchu

@ company.com')

Query OK, 1 row affected (0.43 sec)

Mysql > select * from members

+-+-

+-+-

| | 1 | Yue | Gao | 1234567 | yuegao@company.com |

+-+-

3 rows in set (0.00 sec)

Note: here you can see that the newly inserted record id field has a value of 5, even if there is no record with an id of 3 or 4 in the table. That is, the auto_increment modifier does not reuse values that have been used.

5) limit keyword

You can use the limit keyword to restrict the result set, which has two parameters, separated by commas, specifying which row to start with and how many rows to display. If it is followed by only one parameter, specifies the number of rows returned from the beginning of the result set.

Mysql > select * from members limit 3

Empty set (0.00 sec)

Mysql > select * from members limit 2

+-- +

+-- +

+-- +

1 row in set (0.01 sec)

Mysql > select * from members limit 1,3

+-+-

+-+-

+-+-

2 rows in set (0.00 sec)

Mysql > select * from members limit 0,3

+-+-

+-+-

| | 1 | Yue | Gao | 1234567 | yuegao@company.com |

+-+-

3 rows in set (0.00 sec)

You can see that the line number starts at 0, and the line numbers of the three records are consecutive. Records with id 2 are deleted and reinserted, and records with id 5 already exist when reinserted, but records with id 2 are still at the top of the list.

Mysql > insert into members values (6, 'Chao',' Zhang', '666666666,' chaozhang@com

Pany.com')

Mysql > select * from members limit 3

+-+-

+-+-

+-+-

1 row in set (0.00 sec)

Mysql > select * from members limit 2

+-+-

+-+-

+-+-

2 rows in set (0.00 sec)

Insert a record with an id of 6, which comes after a record with an id of 5. Insert two more records to observe:

Mysql > insert into members values (8, 'Chen',' Zhang', '888888888,' chenzhang@com

Pany.com')

Query OK, 1 row affected (0.43 sec)

Mysql > insert into members values (7, 'Yifei',' Yin', '77777777,' yfyin@company.

Com')

Query OK, 1 row affected (0.42 sec)

Mysql > select * from members limit 5 and 6

+-+-

+-+-

+-+-

1 row in set (0.00 sec)

Mysql > select * from members limit 4

+-+-

+-+-

| | 7 | Yifei | Yin | 7777777 | yfyin@company.com | |

+-+-

2 rows in set (0.01sec)

Mysql > select * from members limit 3

+-+-

+-+-

| | 7 | Yifei | Yin | 7777777 | yfyin@company.com | |

+-+-

3 rows in set (0.00 sec)

As you can see, whether you reinsert deleted records or let records with higher id values insert first than records with lower id values, the order of row numbers seems to be determined by the size order of the id fields. The result of "select * from members;" reflects the order of line numbers.

Mysql > select * from members

+-+-

+-+-

| | 1 | Yue | Gao | 1234567 | yuegao@company.com |

| | 7 | Yifei | Yin | 7777777 | yfyin@company.com | |

+-+-

6 rows in set (0.00 sec)

To determine this guess, do another set of experiments:

Mysql > create table members_temp

-> (

-> id int (3)

-> fname varchar (20)

-> lname varchar (20)

Tel varchar (15)

> email varchar (50)

->)

Query OK, 0 rows affected (0.48 sec)

Mysql > insert into members_temp (id, fname, lname) values (1, 'Yue',' Gao')

Query OK, 1 row affected (0.43 sec)

Mysql > insert into members_temp (id, fname, lname) values (2, 'Feng',' Song')

Query OK, 1 row affected (0.03 sec)

Mysql > insert into members_temp (id, fname, lname) values (5, 'Chen',' Chu')

Query OK, 1 row affected (0.43 sec)

Mysql > select * from members_temp

+-+

+-+

| | 1 | Yue | Gao | NULL | NULL | |

+-+

3 rows in set (0.00 sec)

Mysql > select * from members_temp limit 2

+-+

+-+

+-+

1 row in set (0.01 sec)

Mysql > select * from members_temp limit 1pr 3

+-+

+-+

+-+

2 rows in set (0.00 sec)

Mysql > select * from members_temp limit 0Pol 3

+-+

+-+

| | 1 | Yue | Gao | NULL | NULL | |

+-+

3 rows in set (0.00 sec)

Mysql > insert into members_temp (id, fname, lname) values (3, 'Yifei',' Yin')

Query OK, 1 row affected (0.42 sec)

Mysql > select * from members_temp

+-+

+-+

| | 1 | Yue | Gao | NULL | NULL | |

+-+

4 rows in set (0.00 sec)

Mysql > select * from members_temp limit 3

+-+

+-+

+-+

1 row in set (0.00 sec)

Mysql > select * from members_temp limit 2

+-+

+-+

+-+

2 rows in set (0.00 sec)

Mysql > delete from members_temp where id = 2

Query OK, 1 row affected (0.42 sec)

Mysql > select * from members_temp

+-+

+-+

| | 1 | Yue | Gao | NULL | NULL | |

+-+

3 rows in set (0.00 sec)

Mysql > insert into members_temp (id, fname, lname) values (2, 'Feng',' Song')

Query OK, 1 row affected (0.03 sec)

Mysql > select * from members_temp

+-+

+-+

| | 1 | Yue | Gao | NULL | NULL | |

+-+

4 rows in set (0.00 sec)

As you can see here, the newly created table does not have an index or a primary key, whether it is to insert a record with a larger id value first than a record with a lower id value, or to reinsert a deleted record, the row number is determined by the order in which the records are inserted.

Mysql > create index id_idx on members_temp (id)

Query OK, 4 rows affected (0.71 sec)

Records: 4 Duplicates: 0 Warnings: 0

Mysql > select * from members_temp

+-+

+-+

| | 1 | Yue | Gao | NULL | NULL | |

+-+

4 rows in set (0.00 sec)

Mysql > delete from members_temp where id = 5

Query OK, 1 row affected (0.03 sec)

Mysql > insert into members_temp (id, fname, lname) values (5, 'Chen',' Chu')

Query OK, 1 row affected (0.04 sec)

Mysql > select * from members_temp

+-+

+-+

| | 1 | Yue | Gao | NULL | NULL | |

+-+

4 rows in set (0.00 sec)

Create an index on the id column, as is the case.

Mysql > alter table members_temp add constraint primary key (id)

Query OK, 4 rows affected (0.64 sec)

Records: 4 Duplicates: 0 Warnings: 0

Mysql > select * from members_temp

+-- +

+-- +

| | 1 | Yue | Gao | NULL | NULL | |

+-- +

4 rows in set (0.01sec)

Mysql > delete from members_temp where id = 2

Query OK, 1 row affected (0.45 sec)

Mysql > insert into members_temp (id, fname, lname) values (2, 'Feng',' Song')

Query OK, 1 row affected (0.03 sec)

Mysql > select * from members_temp

+-- +

+-- +

| | 1 | Yue | Gao | NULL | NULL | |

+-- +

4 rows in set (0.00 sec)

Mysql > select * from members_temp limit 3

+-- +

+-- +

+-- +

1 row in set (0.00 sec)

Mysql > select * from members_temp limit 2

+-- +

+-- +

+-- +

2 rows in set (0.00 sec)

Mysql > select * from members_temp limit 1 and 4

+-- +

+-- +

+-- +

3 rows in set (0.00 sec)

Mysql > select * from members_temp limit 0Pol 4

+-- +

+-- +

| | 1 | Yue | Gao | NULL | NULL | |

+-- +

4 rows in set (0.00 sec)

Mysql > select * from members

+-+-

+-+-

| | 1 | Yue | Gao | 1234567 | yuegao@company.com |

| | 7 | Yifei | Yin | 7777777 | yfyin@company.com | |

+-+-

6 rows in set (0.00 sec)

After the primary key is added to the id column, the row number becomes determined by the size order of the id column, which proves the previous conjecture.

Thank you for your reading, the above is the content of "how to modify the MySQL root user password", after the study of this article, I believe you have a deeper understanding of how to modify the MySQL root user password, 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.

*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.