Summary of frequently asked questions about MySQL database

1. Regarding the issue of reassigning initial values ​​to auto-increment fields?

ALTER TABLE tbl AUTO_INCREMENT = 1;

2. How to implement the function of auto-increment field in MySQL?

CREATE table abc(id int(10) not null auto_incremnet PRimary key,

name varchar(10) not null,

address varchar(200) not null,

postcode char(6) not null

);

This creates a table, and the id subfield of this table grows automatically.

You can also add such fields to a created table as follows:

alter table tb_name add id int(10) not null auto_increment first;

or

alter table tb_name add id int(10) not null auto_increment;

3. How to change the user password in mysql?

a, under the mysql/bin/ directory

./mysqladmin -u [user name such as: root] -p [old password, if there is no password, leave it blank] passWord [new password]

./mysqladmin -uroot -p123456 password 456789

Username: root Original password: 123456 New password: 456789

b. Enter mysql as root user

mysql> use mysql

mysql>update user set Password=password('newpassword ' ) where User='root';

mysql>flush privileges;

Pay attention to the case.

4. How to connect to mysql remotely

(1) Enter mysql and create a new user xuys:

Format: grant permission on database name. table name user@login host identified by "user password" ;

grant select,update,insert,delete on *.* to xuys@192.168.88.234 identified by "xuys1234";

View the results, execute:

use mysql;

select host,user,password from user;

You can see that the xuys user just created is already in the user table. The host field represents the logged-in host. Its value can be ip or

host name. Changing the value of the host field to % means that you can log in to the mysql server as the xuys user on any client machine. It is recommended to set it during development. for%.

update user set host = '%' where user = 'xuys';

(2) mysqladmin -uroot -ppwd reload

mysqladmin -uroot -ppwd shutdown

(3)./mysqld_safe --user=root &

Remember: any modification to the authorization table requires reloading, that is, step 3.

If you still cannot connect from the client after the above 3 steps, please perform the following operations,

Insert a record in the db table of the mysql database:

use mysql;

insert into db values('192.168.88.234' ,'%','xuys','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y',' Y','Y');

update db set host = '%' where user = 'xuys';

Repeat steps 2 and 3 above.

The above is a summary of frequently asked questions about MySQL database. For more related articles, please pay attention to the PHP Chinese website (m.sbmmt.com)!