MySQL Remote Login

Mysql does not allow remote connection by default, because it poses a great security risk.

You need to manually add users who can remotely access the database.

Method 1. log on to mysql locally, change the "host" item in the "user" table in the "mysql" database, and change "localhost" to "%"

# Mysql-u root-proot
Mysql> use mysql;
Mysql> update user set host = '%' where user = 'root ';
Mysql> select host, user from user;

Method 2: direct authorization (recommended)

Use the root user on any host, password: youpassword (your root password) to connect to the mysql server:

# Mysql-u root-proot
Mysql> grant all privileges on *. * TO 'root' @ '%' identified by 'youpassword' with grant option;

Allow the address 202.11.10.253 to connect to all mysql databases with the root user and password dboomysql, and grant the select, insert, update, and delete permissions.

# Mysql-u root-proot
Grant select, insert, update, delete on *. * to root @ "202.11.10.253" Identified by "dboomysql ";

Allow the address 202.11.10.253 to use the root user and password dboomysql to connect to all mysql Databases and grant all permissions.

# Mysql-u root-proot
Grant all on *. * to root @ "202.11.10.253" Identified by "dboomysql"

After the Mysql service is restarted, you can use the graphic database management software (Navicat MySQL is a good choice) to remotely access Mysql.

Mysql tips

1. Modify the root password in mysql:
Shell> mysql-u root-p
Mysql> set password for root = PASSWORD ("root ");

2. Remote logon to mysql server: (of course, the server must first authorize a remote user)
Shell> mysql-h host-u user-p

3. Open the database: use dbname;
Show all databases: show databases;
Display All tables in mysql: use mysql first, and then show tables;
Displays the table column information: describe user; (displays the user table information in the mysql database );

4. Create a full Super User that can connect to the server from any address, but you must use a password something to do this
Grant all privileges on *. * TO monty @ localhost identified by 'something' with grant option;
Grant all privileges on *. * TO monty @ "%" identified by 'something' with grant option;

5. Delete authorization:
Revoke all privileges on *. * FROM root @ "% ";
USE mysql;
Delete from user WHERE User = "root" and Host = "% ";
Flush privileges;

6. Create a User custom to log on to weiqiong.com on a specific client and access the specific database bankaccount.
Mysql> grant select, INSERT, UPDATE, DELETE, CREATE, drop on bankaccount .*
TO custom@weiqiong.com identified by 'stupid ';

7. Rename the table:
Alter table t1 RENAME t2;

To change column a from INTEGER to tinyint not null (same name ),
Change Column B from CHAR (10) To CHAR (20), rename it, and change from B to c:
Alter table t2 MODIFY a tinyint not null, CHANGE B c CHAR (20 );

Add a new TIMESTAMP column named d:
Alter table t2 ADD d TIMESTAMP;

Add an index on column d and set column a as the primary key:
Alter table t2 add index (d), add primary key ();

Delete column c:
Alter table t2 drop column c;

Add a new AUTO_INCREMENT integer column named c:
Alter table t2 ADD c int unsigned not null AUTO_INCREMENT, add index (c );
Note that we have indexed c, because the AUTO_INCREMENT column must be indexed, and we declare c as not null,
Because the indexed Column cannot be NULL.

8. Delete record:
Delete from t1 where c> 10;

6. Change several rows:
UPDATE t1 SET user = weiqiong, password = weiqiong;

7. Create an index with the first 10 characters of the name column:
Create index part_of_name ON customer (name (10 ));