Install and configure a remote connection to MySQL in Linux

Install MySQL
1. Download the MySQL Installation File
The following two files are required to install MYSQL:
MySQL-server-5.1.7-0.i386.rpm

To download the above two RPM files, locate the "server" and "client programs" items and find the items "downloads" under "statically linked against glibc 2.2.5) of X86 generic RPM (statically linked against.
2. Install MySQL
The RPM file is a software installation package developed by Red Hat. RPM frees Linux from complicated procedures when installing software packages. The frequently used parameter of this command during installation is-IVH, where I indicates that the specified RMP package will be installed, and V indicates the detailed information during installation, h indicates that the "#" symbol appears during the installation to display the current installation process. This symbol will not stop until the installation is complete.
1) install the server
Run the following command in the directory with two RMP files:
[Root @ test1 local] # rpm-IVH MySQL-server-5.1.7-0.i386.rpm MySQL-client-5.1.7-0.i386.rpm
Reminder:
If the following
Error: Failed dependencies:
Perl (DBI) is needed by MySQL-server-5.1.16-0.glibc23.i386
Please use
Install Yum install Perl-DBI. Of course, you need to try different releases.
The following information is displayed.
Warning: MySQL-server-5.1.7-0.i386.rpm
Signature: nokey, key ID 5072e1f5
Preparing... ######################################## ### [100%]
1: mysql-server ##################################### ###### [100%]
...... (Omitted)
/Usr/bin/mysqladmin-u Root Password 'new-password'
/Usr/bin/mysqladmin-u root-H test1 password 'new-password'
...... (Omitted)
Starting mysqld daemon with databases from/var/lib/MySQL
If the preceding information is displayed, the Server installation is complete. Run netstat to check whether the MySQL port is opened. If yes, the service is started and the installation is successful. The default MySQL port is 3306.
[Root @ test1 local] # netstat-Nat
Active Internet connections (servers and established)
PROTO Recv-Q send-Q local address foreign address State
TCP 0 0 0.0.0.0: 3306 0.0.0.0: * listen
The above shows that the MySQL service has been started.
2) install the client
Run the following command:
[Root @ test1 local] # rpm-IVH MySQL-client-5.1.7-0.i386.rpm
Warning: MySQL-client-5.1.7-0.i386.rpm: V3 DSA Signature: nokey, key ID 5072e1f5
Preparing... ######################################## ### [100%]
1: mysql-client ##################################### ###### [100%]
The installation is complete.
Use the following command to connect to MySQL and test whether the connection is successful.
3. log on to MySQL

The command used to log on to MySQL is mysql. the syntax of MySQL is as follows:
MySQL [-u username] [-H host] [-P [Password] [dbname]
Username and password are the username and password of MySQL respectively. The initial management account of MySQL is root, and there is no password. Note: This root user is not a Linux system user. The default MySQL user is root. Because there is no password at first, you only need to type MySQL for the first time.
[Root @ test1 local] # MySQL
Welcome to the MySQL monitor. commands end with; or \ G.
Your MySQL connection ID is 1 to server version: 4.0.16-Standard
Type 'help; 'or' \ H' for help. Type '\ C' to clear the buffer.
Mysql>
The prompt "mysql>" appears. Congratulations! The installation is successful!
The logon format after the password is added is as follows:
Mysql-u root-P
Enter Password: (enter the password)
-U is followed by the user name.-P requires the password. Press enter and enter the password at the Enter password.

Note: This MySQL file is located in the/usr/bin directory. It is not a file with the Startup file/etc/init. d/MySQL described later.

4. Several important MySQL Directories

After MySQL is installed, its database files, configuration files, and command files are not installed in the same directory as SQL Server by default. It is very important to understand these directories, especially for Linux beginners, the directory structure of Linux itself is complicated. If you cannot figure out the installation directory of MySQL, you won't be able to learn it in depth.

The following describes these directories.

1. Database directory
/Var/lib/MySQL/

2. Configuration File
/Usr/share/MySQL (MySQL. server command and configuration file)

3. Related commands
/Usr/bin (commands such as mysqladmin mysqldump)

4. Start the script
/Etc/rc. d/init. d/(directory for starting the script file MySQL)
5. Change the logon Password

MySQL does not have a password by default. It is self-evident that the password is added after installation.

1. Commands
Usr/bin/mysqladmin-u Root Password 'new-password'
Format: mysqladmin-u username-P old Password New Password

2. Example
Example 1: Add a 123456 password to the root user.
Type the following command:
[Root @ test1 local] #/usr/bin/mysqladmin-u Root Password 123456
Note: because the root account does not have a password at the beginning, the old-P password can be omitted.

3. test whether the modification is successful
1) login without a password
[Root @ test1 local] # MySQL
Error 1045: Access denied for user: 'root @ localhost' (using password: No)
An error is displayed, indicating that the password has been modified.
2) log on with the modified Password
[Root @ test1 local] # mysql-u root-P
Enter Password: (enter the password 123456 after modification)
Welcome to the MySQL monitor. commands end with; or \ G.
Your MySQL connection ID is 4 to server version: 4.0.16-Standard
Type 'help; 'or' \ H' for help. Type '\ C' to clear the buffer.
Mysql>
Successful!
You can use the mysqladmin command to change the password or the database to change the password.

6. Start and Stop

1. Start
After MySQL is installed, run the following command to start MySql in the/etc/init. d directory.
[Root @ test1 init. d] #/etc/init. d/MySQL start

2. Stop
/Usr/bin/mysqladmin-u root-P Shutdown

3. Automatic Start
1) Check whether MySQL is in the Auto Start List
[Root @ test1 local] #/sbin/chkconfig -- list
2) Add MySQL to the startup Service Group of your system.
[Root @ test1 local] #/sbin/chkconfig -- add MySQL
3) Delete MySQL from the startup Service Group.
[Root @ test1 local] #/sbin/chkconfig -- del MySQL
7. Change the MySQL directory

The default data file storage directory of MySQL is/var/lib/MySQL. To move the directory to/home/data, perform the following steps:

1. Create a data directory under the Home Directory
CD/home
Mkdir data

2. Stop the MySQL service process:
Mysqladmin-u root-P Shutdown

3. Move the entire/var/lib/MySQL directory to/home/Data
MV/var/lib/MySQL/home/data/
In this way, the MySQL data file is moved to/home/data/MySQL.

4. Find the my. CNF configuration file.
If my. for the CNF configuration file, go to/usr/share/MySQL/and find *. copy one of the CNF files to/etc/and change it to my. CNF. The command is as follows:
[Root @ test1 MySQL] # cp/usr/share/MySQL/my-medium.cnf/etc/My. CNF

5. Edit the MySQL configuration file/etc/My. CNF.
To ensure that MySQL works properly, you must specify the location where the mysql. Sock file is generated. Change socket =/var/lib/MySQL. Sock to/home/MySQL. Sock. The procedure is as follows:
VI my. CNF (use the VI tool to edit the my. CNF file and find the following data to modify)
# The MySQL Server
[Mysqld]
Port = 3306
# Socket =/var/lib/MySQL. Sock)
Socket =/home/data/MySQL. Sock (add this line)

6. Modify the MySQL STARTUP script/etc/rc. d/init. d/MySQL
Finally, you need to modify the MySQL STARTUP script/etc/rc. d/init. d/MySQL: change the path on the Right of datadir =/var/lib/MySQL to your actual storage path: Home/data/MySQL.
[Root @ test1 etc] # vi/etc/rc. d/init. d/MySQL
# Datadir =/var/lib/MySQL (comment this row)
Datadir =/home/data/MySQL (add this row)

7. Restart the MySQL service.
/Etc/rc. d/init. d/MySQL start
Or use the reboot command to restart Linux.
If it works properly, it will succeed. Otherwise, check again against the previous seven steps.

VIII. Common MySQL operations

Note: Each Command in MySQL must end with a semicolon.

1. display the database
Mysql> show databases;
+ ---------- +
| Database |
+ ---------- +
| MySQL |
| Test |
+ ---------- +
2 rows in SET (0.04 Sec)
MySQL has just been installed with two databases: MySQL and test. The MySQL database is very important. It contains MySQL system information. We change the password and add new users. In fact, we use the relevant tables in this database for operations.

2. display tables in the database
Mysql> use MySQL; (open the database. to operate on each database, open the database, similar to FOXPRO)
Database changed

Mysql> show tables;
+ ----------------- +
| Tables_in_mysql |
+ ----------------- +
| Columns_priv |
| DB |
| Func |
| Host |
| Tables_priv |
| User |
+ ----------------- +
6 rows in SET (0.01 Sec)

3. display the data table structure:
Describe table name;

4. display the records in the table:
Select * from table name;
For example, the user table records in the MySQL database are displayed. All users who can operate on mysql users are in this table.
Select * from user;

5. database creation:
Create Database database name;
For example, create a database named aaa
Mysql> create databases AAA;
6. Create a table:
Use Database Name;
Create Table Name (field setting list );
For example, if you create a table name in the newly created AAA database, the table has four fields: ID (serial number, auto-increment), XM (name), Xb (gender), and csny (date of birth ).
Use AAA;
Mysql> Create Table Name (ID int (3) auto_increment not null primary key, XM char (8), XB char (2), csny date );
You can use the describe command to view the created table structure.
Mysql> describe name;

+ ------- + --------- + ------ + ----- + --------- + ---------------- +
| FIELD | type | null | key | default | extra |
+ ------- + --------- + ------ + ----- + --------- + ---------------- +
| ID | int (3) | pri | null | auto_increment |
| XM | char (8) | Yes | null |
| XB | char (2) | Yes | null |
| Csny | date | Yes | null |
+ ------- + --------- + ------ + ----- + --------- + ---------------- +

7. Add records
For example, add several related records.
Mysql> insert into name values ('', 'zhang san', 'mal', '2017-10-01 ');
Mysql> insert into name values ('', 'baiyun ', 'female', '2017-05-20 ');
The SELECT command can be used to verify the result.
Mysql> select * from name;
+ ---- + ------ + ------------ +
| ID | XM | XB | csny |
+ ---- + ------ + ------------ +
| 1 | Zhang San | male |
| 2 | Baiyun | female | 1972-05-20 |
+ ---- + ------ + ------------ +

8. Modify records
For example, change the date of birth of John
Mysql> Update name set csny = '2017-01-10 'Where XM = 'zhang san ';

9. delete records
For example, delete the records of Michael Jacob.
Mysql> Delete from name where XM = 'zhang san ';

10. Delete databases and tables
Drop database database name;
Drop table name;

9. Add mysql users

Format: grant select on database. * To username @ login host identified by "password"
Example 1: Add a user user_1 with a password of 123 so that he can log on to any host and have the permission to query, insert, modify, and delete all databases. First, use the root user to connect to MySQL, and then type the following command:

Mysql> grant select, insert, update, delete on *. * To user_1 @ "%" identified by "123 ";
In example 1, the added user is very dangerous. If you know the user_1 password, then he can log on to your MySQL database on any computer on the Internet and do whatever he wants. For the solution, see Example 2.

Example 2: Add a user_2 password of 123 so that the user can only log on to localhost, you can also query, insert, modify, and delete the database AAA (localhost refers to the local host, that is, the host where the MySQL database is located), so that the user knows the password of user_2, he cannot directly access the database from the Internet, and can only operate the AAA database through the MySQL host.

Mysql> grant select, insert, update, delete on AAA. * To user_2 @ localhost identified by "123 ";

If a new user cannot log on to MySQL, run the following command during logon:

Mysql-u user_1-p-h 192.168.113.50 (-H is followed by the IP address of the host to be logged on)

10. backup and recovery

1. Backup

For example, back up the AAA library created in the previous example to the back_aaa file.

[Root @ test1 root] # cd/home/data/MySQL (go to the database directory, this example library has been transferred from Val/lib/MySQL to/home/data/MySQL, see section 7 above)
[Root @ test1 MySQL] # mysqldump-u root-p -- opt AAA> back_aaa

2. Recovery

[Root @ test MySQL] # mysql-u root-p ccc <back_aaa
11. Change the MySQL password
Method 1
[Root @ test1 root] # mysql-u root-P
# Enter your MySQL password, which is empty by default.
Enter password:
# Execute the following statement
Mysql> set password for 'root' @ 'localhost' = PASSWORD ("your new password ");
Method 2
Mysqladmin-u root-P password mypasswd
After entering this command, you need to enter the original root password, and then the root password will be changed to mypasswd.
Change the root in the command to your username, and you can change your password.
Add users and grant permissions
Grant all privileges on *. * To primadbadmin @ localhost
Identified by 'au6788akadg' with grant option;
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;
You can run the grant command in the following format:
Grant permission on database name. Table name to new user name @ host name identified by 'Password ';
Summary of common MySQL database operation commands
1. Connect to MySQL:

Format: mysql-H host address-u user name-P User Password
1. Example 1: connect to MySQL on the local machine.
First, open the DOS window, enter the directory mysqlbin, type the command mysql-uroot-P, and press enter to prompt you to enter the password.
If you have just installed MySQL, the Super User Root does not have a password, so press enter to enter MySQL, MySQL
The prompt is: mysql>
2. Example 2: connect to MySQL on the remote host. Assume that the IP address of the remote host is 110.110.110.110, the user name is root, and the password is
The code is abcd123. Enter the following command:
Mysql-h110.110.110.110-uroot-pabcd123
(Note: you do not need to add spaces for u and root. The same applies to others)
3. Exit MySQL command: exit (Press ENTER)

2. Change the password:

Format: mysqladmin-u username-P old Password New Password
1. Example 1: Add a password ab12 to the root user. First, enter the directory mysqlbin in DOS, and then type the following command
Mysqladmin-uroot-Password ab12
Note: because the root account does not have a password at the beginning, the old-P password can be omitted.
2. Example 2: Change the root password to djg345.
Mysqladmin-uroot-pab12 password djg345

3. Add new users: (Note: Unlike the above, the following commands in the MySQL environment are followed by a score.
As the command Terminator)

Format: grant select on database. * To username @ login host identified by "password"
Example 1: Add a user named "test1" with the password "ABC" so that the user can log on to any host and have the permission to query, insert, modify, and delete all databases. First, use the root user to connect to MySQL, and then type the following command:
Grant select, insert, update, delete on *. * To test1 @ "%" identified by "ABC ";
However, the User Added in Example 1 is very dangerous. If someone knows the password of test1, then he can log on to your MySQL database on any computer on the Internet and do whatever he wants for your data. For the solution, see Example 2.
Example 2: Add a user named "Test2" with the password "ABC" so that the user can only log on to localhost, you can also query, insert, modify, and delete the database mydb (localhost refers to the local host, that is, the host where the MySQL database is located), so that the user knows the password of Test2, he cannot access the database directly from the Internet, but can only access the database through the web pages on the MySQL host.
Grant select, insert, update, delete on mydb. * To Test2 @ localhost identified by "ABC ";
If you do not want Test2 to have a password, you can run another command to remove the password.
Grant select, insert, update, delete on mydb. * To Test2 @ localhost identified "";

Example 3: Add a WWW user with full control permissions on the WWW database:
Grant all privileges on www. * to www @ localhost identified by "wwwpass ";
If you change localhost to %, you can access the database on any host and add "".
Grant all privileges on www. * to www @ "%" identified by "wwwpass ";

4. Display commands

1. display the Database List:
Show databases;
At the beginning, there were only two databases: MySQL and test. The MySQL database contains MySQL system information. We need to change the password and
Newly Added users are actually operated using this database.
2. display the data tables in the database:
Use MySQL; // open the database. If you have learned FOXBASE, you will not be unfamiliar with it.
Show tables;
3. display the data table structure:
Describe table name;
4. database creation:
Create Database database name;
5. Create a table:
Use Database Name;
Create Table Name (field setting list );
6. Delete databases and tables:
Drop database database name;
Drop table name;
7. Clear records in the table:
Delete from table name;
8. Display records in the table:
Select * from table name;

5. An instance for creating a database, creating tables, and inserting data
Create Table if not exists
Create Database school; // create a database School
Use school; // open the school library
Create Table teacher // create table teacher
(
Id int (3) auto_increment not null primary key, // ID is set as the primary keyword and automatically set the value, that is
You do not need to write content to the ID field when adding
Name char (10) Not null,
Address varchar (50) default 'shenzhen ', // set the default value to Shenzhen.
Year date
); // Table creation ends

// Insert fields as follows
Insert into teacher values ('', 'glengang ', 'shenzhen Zhongyi', '2017-10-10 '); // The ID does not need to be written.
Insert into teacher values ('', 'jack', 'shenzhen Zhongyi ', '2017-12-23 ');

6. Modify the database structure

1. Add a field
Alter table dbname add column <field Name> <field Options>
2. Modify Fields
Alter table dbname change <old field Name> <new field Name> <option>
3. delete a field
Alter table dbname drop column <field Name>
4. Modify the table name
Alter table jsxt Rename as jsxt_tmp;
Alter table article add primary key (ID );
Alter table Article modify ID int (10) auto_increment;

Load data infile "/home/mark/data. SQL" into Table orders;
To put it simply, the content in the file data. SQL will be imported into the table orders, like the mysqlimport tool.
Command

There are some optional parameters. For example, if you want to import data from your computer to a remote database server, you can
Use

Command:
Load data local infile "C: \ mydocs \ SQL .txt" into Table orders;

Mysqldump-P database name Table Name> meetageek_orders.txt

Mysql-u-p database name <*. SQL // import *. SQL data

Mysqladmin-u-p Database Name

VII. MySQL remote connection to user instance Solution

After MySQL is installed, there is only one root user with super management permissions, and root restrictions can only be used on the local database. What if we want to remotely manage MySQL? In fact, we need to add a Super User with super management permissions and remote access. There are two ways to achieve this in MySQL, we will add a super permission management user admin as an example.

You can add new users by issuing the grant statement: first, log on to MySQL with the root user on the database machine (do not tell me how to log on ?), Then:

Mysql> grant all privileges on *. * to admin @ localhost identified by 'something' with grant option;
Mysql> grant all privileges on *. * to admin @ "%" identified by 'something' with grant option;

In the first sentence, an admin user is added to authorize access through a local machine (localhost) with the password "something ". The second sentence is to authorize the admin user to initiate access from any other host (wildcard % ).

You can also directly add the same user access information by issuing an insert statement:

Mysql> insert into user values ('localhost', 'admin', password ('something'), 'y ', 'y', 'y ')
Mysql> insert into user values ('%', 'admin', password ('something'), 'y ', 'y', 'y ')

Depending on your MySQL version, for the above, you may have to use a different number of 'y' values (versions earlier than 3.22.11 have fewer permission columns ).

Verification Method: Enter the mysql-H host name or IP address-u username-P password in the command line.

Reprint please indicate the source http://blog.csdn.net/shimiso

Technical Exchange Group: 173711587