Install MySQL RPM package

Mysql Oracle official download type exclusive seems a headache for us to choose
Download MySQL Community Server

Http://dev.mysql.com/downloads/mysql/

On this download site, select the platform "CMDL & red hat liunx 5 ".

Select installation package MySQL-5.6.12-1.linux_glibc2.5.x86_64.rpm-bundle 303 M

Decompress the package in windows and upload the two files containing the words SERVER and CLIENT to linux.

I uploaded it to the/home directory, and then root user rpm-ivh mysql-service-5.6.12-...... rpm

The two packages are successfully installed.

Error reported during startup

Mysql-u root
Error 1045 (28000): access denied for user 'root' @ 'localthost' (using password: NO)

[Root @ loaclhost]/usr/bin/mysqld_safe -- usr = mysql -- skip-grant-tables -- skip-networking &

Enable the second terminal

[Root @ loaclhost]/usr/bin/mysql-u root mysql
@ Finally got in

Mysql> update user set password = password ('yourword') where user = 'root ';
Mysql> flush PRIVILEGES;
Mysql> quit

[Root @ loaclhost] service mysql restart

[Root @ loaclhost]/usr/bin/mysql-uroot-p

Succeeded

Directory1. Database directory/var/lib/mysql/2. Configuration File/usr/share/mysql (mysql. server commands and configuration files) 3. Related commands/usr/bin (mysqladmin mysqldump and other commands) 4. STARTUP script/etc/rc. d/init. d/(directory for starting the script file 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 opt directory
Cd/opt
Mkdir mysql_rpm_5612/data/

2. Stop the MySQL service process:
Mysqladmin-u root-p shutdown

3. Move the entire/var/lib/mysql directory to/opt/mysql_rpm_5612/data/
Mv/var/lib/mysql/opt/mysql_rpm_5612/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 =/opt/mysql_rpm_5612/data // mysql/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 =/opt/mysql_rpm_5612/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 7 steps.

Then check localhost. localdomain. err in the directory.
Found: Can't start server: Bind on unix socket: Permission denied
Do you alread have another mysqld server running on socket:/opt/mysql_rpm_5612/data/mysql. sock?

I realized that/etc/my. cnf was wrong.

Server mysl start

OK

Mysql-u root-p

Enter password:

Error 2002 (HY000): can't connect to local mysql server through socket '/var/lib/mysql. sock (2 )'

Mysql-u root-p is a client segment Software Package

Therefore, add

[Client]

Socket =/opt/mysql_rpm_5612/Data // MySQL/MySQL. Sock

Log On again:

Mysql-u root-P

Enter password:

OK

Mysql> show databases;

Error 1820 (hy000): You must set password before executing this statement

It's very strange. I logged in with my password. Why do I still ask for a password.
For more information, see http://dev.mysql.com/doc/refman/5.6/en/alter-user.html. After performing the following operations, you will be OK:

 

Mysql> Create Database yan1;
Error 1820 (hy000): You must set password before executing this statement
Mysql> SET Password = PASSWORD ('20140901 ');
Query OK, 0 rows affected (0.03 Sec)

Mysql> Create Database yan1;
Query OK, 1 row affected (0.00 Sec)

That is, use mysql> SET Password = PASSWORD ('123'); in this case, reset the password! It's really hard.
This dear friend also encountered the same problem.

Mysql> show databases;

, Display 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 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