Installing MySQL in Linux and solutions to some common problems _ MySQL-mysql tutorial

1. download MySql 1. download MySql
Open http://www.mysql.com/downloads/mysql/#downloads download in browser

My downloaded version is Red Hat 5 version of http://www.mysql.com/downloads/mirror.php? Id = 407552

Upload the file to the server, or download the file directly using wget on the server.
(I stored it in the/opt/tools directory on the server)

II. decompress the tar file
Execute the command: tar-xvf MySQL-5.5.23-1.rhel5.x86_64.tar

3. install the MySql server (these rpm files are installed as needed)
I'm here to install the MySQL-server-5.5.23-1.rhel5.x86_64.rpm server
Run command: rpm-ivh MySQL-server-5.5.23-1.rhel5.x86_64.rpm

If the preceding information is displayed, the server installation is complete.
Check whether the test is successful. 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.

4. start the mysql service
Run the command: service mysql start
Run the following command: netstat-ant to check whether the MySQL port is opened: Port 3306
Tcp 0 0: 3306: * LISTEN

5. install the client
Run command: rpm-ivh MySQL-client-5.5.23-1.rhel5.x86_64.rpm
After the installation is complete, use the following operation commands to connect to mysql and test whether the installation is successful.

6. 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 usernames and passwords of MySQL respectively, and the initial management account of mysql is root without a password.
Note: This root user is not a Linux user. The default MySQL User is root. because there is no password at first, you only need to type mysql for the first time.
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.

VII. 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)

8. change the logon password
MySQL does not have a password by default. it is self-evident that the password is added after installation.

1. command (the console also tells us the following command to change the password when the installation is successful)
/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.

9. start and stop

Run the command: service mysql status to view the mysql startup status.

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

Or: service mysql start

2. stop
/Usr/bin/mysqladmin-u root-p shutdown

Or: service mysql stop

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

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

11. 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)

12. 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 "change mysql Directory)
[Root @ test1 mysql] # mysqldump-u root-p -- opt aaa> back_aaa

2. recovery

[Root @ test mysql] # mysql-u root-p ccc <back_aaa

13. common mysql database Operation Commands

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 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 name;
Drop table name;

Certificate -------------------------------------------------------------------------------------------------------------------------------------------
~~ Installation completed ~~
Certificate -------------------------------------------------------------------------------------------------------------------------------------------

Solution:
1. after the database is installed, we need to connect remotely. the error 1130-Host 'XXX. xxx. xxx. xx' is not allowed to connect to this MySQL serverConnection closed by foreign host.
Error No.: 1130
Problem Analysis: MySQL does not have the permission to enable remote logon.
Solution:
Check whether the database server you entered is correct and authorize the web server to connect to it. perform the following operations:

There are two major steps to enable the MySQL remote login account:

1. check whether the firewall on the server does not block port 3306.

The default port of MySQL is 3306. make sure that the firewall does not block port 3306. Otherwise, the remote connection to MySQL cannot be established through Port 3306.

If you specify another port when installing MySQL, enable the port used by MySQL in the firewall.

If you do not know how to set up a firewall on your server, contact your server administrator.

2. added support for remote connection to MySQL Users and authorization.

1) first log on to MySQL with the root account

On the Linux host, enter the following command in the command prompt line.

[Root @ database-server ~] # Mysql-u root-p
Enter password: (Enter the password)

2) create a remote login user and authorize
Command: grant all PRIVILEGES on testdb. * to shanhy @ '192. 123.123.123 'identified by '123 ';

The preceding statement grants all permissions of the testdb database to the shanhy user, allows the shanhy user to remotely log on to the IP address 123.123.123.123, and sets the shanhy user password to 123456.

The following describes all the parameters one by one:

All PRIVILEGES indicates that all permissions are granted to the specified user. here, you can also replace it with a specific permission, such as select, insert, update, delete, create, and drop, separate specific permissions with commas.

Testdb. * indicates the table to which the preceding permission applies. testdb indicates the database, and * indicates all the tables. Therefore, we can infer that: authorize "*. * ", authorize all tables of a database to" database name. * ", authorize a table of a database as" database name. table name ".

Shanhy indicates the user you want to authorize. This user can be an existing user or a non-existent user.

123.123.123.123 indicates the IP address that allows remote connection. if you want to restrict the IP address, set it to "%.

123456 is the user's password.

Use: grant all PRIVILEGES on *. * to root @ '%' with grant option; // grant data access permissions to any host.

After the preceding statement is executed, the following statement can take effect immediately.

Flush privileges;

Use the above authorization method or try the following table change operation:
1. mysql> use mysql;
2. mysql> update user set host = '%' where user = 'root ';
3. mysql> select host, user from user;

II. modify the mysql configuration file and adjust the database encoding and table name case
1. modify the configuration
Add under [client]
Default-character-set = utf8

Add under [mysqld]
Init-connect = 'set NAMES utf8'
Character-set-server = utf8
Collation-server = utf8_general_ci
# (Note that after mysql is installed in linux, it is the default value: Case Sensitive to table names, case insensitive to column names; lower_case_table_names = 0 0: Case Sensitive, 1: case insensitive)
Lower_case_table_names = 1
# (Set the maximum number of connections. the default value is 151. The maximum number of connections allowed by the MySQL server is 16384)
Max_connections = 1000

Add under [mysql]
Default-character-set = utf8

PS: There is nothing advanced about this. the important point is to pay attention to the version. There are a lot of posts on the Internet that need to be added with default-character-set = utf8 under [mysqld, in fact, Version 5.0 (>) and later are not supported. if you modify this way, your mysql cannot be started and an error is reported. The message "did not save the PID when you exited last time ?? "(Probably this error message)
Or add the parameter mysqld -- default-character-set = utf8 at startup.
Or add the parameter:./configure -- width-charset = utf8 during compilation.

2. Save (if you use vi for operation, an error will be reported during saving, because this file is a read-only attribute, you can use wq! Force save and exit ). Restart mysql.

3. modify the my. cnf configuration file and set the default engine to InnoDB. add the following two sentences under [mysqld.

Default-storage-engine = InnoDB
Default_table_type = InnoDB

Open the following content at the same time:

Innodb_data_home_dir =/var/lib/mysql/
Innodb_data_file_path = ibdata1: 10 M: autoextend
Innodb_log_group_home_dir =/var/lib/mysql/
# You can set .. _ buffer_pool_size up to 50-80%
# Of RAM but beware of setting memory usage too high
Innodb_buffer_pool_size = 16 M
Innodb_additional_mem_pool_size = 2 M
# Set .. _ log_file_size to 25% of buffer pool size
Innodb_log_file_size = 5 M
Innodb_log_buffer_size = 8 M
Innodb_flush_log_at_trx_commit = 1
Innodb_lock_wait_timeout = 50

4. modify the maximum number of connections. the default value is 151 and the value is 500.

# Vi/etc/my. conf

[Mysqld]

Max_connections = 500

Verify configuration
Show variables like 'max _ con _ % ';

5. mysql cannot create functions to solve the problem

The following is a reference clip:

Error Code: 1418

This function has none of DETERMINISTIC, no SQL, or reads SQL DATA in its declaration and binary logging is enabled (you * might * want to use the less safe log_bin_trust_function_creators variable)

(0 MS taken)

The solution is as follows:

Add a line of log-bin-trust-function-creators = 1 after marking [mysqld] in the my. ini (my. cnf in linux) file

Restart the mysql service.

VI. mysql 1045 error

The following is a reference clip:
Enter the mysql command line on the mysql server
Execute UPDATE user SET Password = PASSWORD ('newpassword') where USER = 'root'
Execute flush privileges;
Then, restart the mysql service by using service mysql restart.

7.How to solve the mysql conflict during centos installation

Preparing... ######################################## ### [100%]

File/usr/share/mysql/charsets/README from install of MySQL-server-community-5.1.44-1.rhel5.i386 conflicts with file from package mysql-libs-5.1.61-4.el6.i686

File/usr/share/mysql/charsets/Index. xml from install of MySQL-server-community-5.1.44-1.rhel5.i386 conflicts with file from package mysql-libs-5.1.61-4.el6.i686

File/usr/share/mysql/charsets/armscii8.xml from install of MySQL-server-community-5.1.44-1.rhel5.i386 conflicts with file from package mysql-libs-5.1.61-4.el6.i686

File/usr/share/mysql/charsets/ascii. xml from install of MySQL-server-community-5.1.44-1.rhel5.i386 conflicts with file from package mysql-libs-5.1.61-4.el6.i686

Solution:

Uninstall mysql: rpm-e -- nodeps mysql-libs-5.1 .*

8. unable to log on after installing mysql 5.6

Error: you must set password before executing this statement
Solution:
The default root password is blank. the landlord directly enters mysql and then changes it. for example:

Mysql> SELECT 1;
ERROR 1820 (HY000): You must set password before executing this statement

Mysql> set password = PASSWORD ('New _ password ');
Query OK, 0 rows affected (0.01 sec)

9. check the default password for mysql5.6

[Root @ shujuku-136 ~] # More. mysql_secret
Or
[Root @ shujuku-136 ~] # More/root/. mysql_secret
# The random password set for the root user at Wed Jun 26 18:23:34 2013 (local time): x7YrI4bP

10. SQL Error 1366 sqlstate HY000
Solution:
1. refer to Mysql official website http://dev.mysql.com/doc/refman/5.1/en/gone-away.html
2. modify my. cnf for linux [mysqld] max_allowed_packet = 32 M
When the MySQL client or mysqld server receives an information packet greater than the value of max_allowed_packet, the "information packet is too large" error is sent and the connection is closed. For some clients, if the communication information package is too large, a "lost connection to MySQL server" error may occur during query.

Both the client and server have their own max_allowed_packet variable. Therefore, if you want to process a large information package, you must add the variable on the client and server. Generally, the default value of max-allowed-packet on the server is 1 MB.

11. Solution to Got error 28 from storage engine in mysql 1030

Error description:
ERROR 1030 (HY000): Got error 28 from storage engine

Error cause:
The SQL statement cannot be executed because the temporary space is insufficient.

Solution:
Point tmpdir to a directory with a large disk space.
1. modify the my. cnf configuration file and add or modify the tmpdir = temporary file directory location under datadir.
2. modify the/etc/rc. d/init. d/mysql startup file to add or modify the tmpdir = temporary file directory location.
Temporary files are usually stored in datadir, such as tmpdir =/home/data/mysql_data/tmp.