Linux installation MySQL and some common problems solutions

First, download MySQL
Browser opens http://www.mysql.com/downloads/mysql/#downloads download

The version I downloaded is the Red Hat 5 version of http://www.mysql.com/downloads/mirror.php?id=407552

Upload to the server, or download it directly on the server wget download mode.
(I'm on the server under directory/opt/tools)

second, unzip the tar file
Execute command: TAR-XVF Mysql-5.5.23-1.rhel5.x86_64.tar

Third, install the MySQL server (these RPM files are installed according to our needs)
I'm here to install the MYSQL-SERVER-5.5.23-1.RHEL5.X86_64.RPM server
Execute command: RPM-IVH mysql-server-5.5.23-1.rhel5.x86_64.rpm

If the above information appears, the server installation is complete.
If the test is successful, run Netstat to see if the MySQL port is open, such as open indicates that the service has been started and the installation is successful. The default port for MySQL is 3306.

iv. start the MySQL service
Execute command: Service MySQL start
Execute command: netstat-ant see if the MySQL port is open with one 3306 port
TCP 0 0::: 3306:::* LISTEN

v. Installation of the client
Execute command: RPM-IVH mysql-client-5.5.23-1.rhel5.x86_64.rpm
After the installation is complete, use some of the following steps to connect to MySQL and test for success.

vi. Login to MySQL
The command to log in to MySQL is MySQL, and MySQL uses the following syntax:
MySQL [-u username] [-h host] [-p[password]] [dbname]
Username and password are mysql user name and password, MySQL's initial management account is root, no password.
Note: This root user is not a Linux system user. MySQL Default user is root, because the initial no password, the first time to enter the only need to type MySQL.
Mysql>
There is a "mysql>" prompt, congratulations, installation success!
The login format after adding the password is as follows:
Mysql-u root-p
Enter Password: (enter password)
Where-u followed by the user name,-p requires a password, enter the password at the input password.

Note: This MySQL file is in the/usr/bin directory, and the boot file/etc/init.d/mysql is not a file.

Vii. Several important directories of MySQL
MySQL installation is not as if SQL Server is installed by default in a directory, its database files, configuration files and command files in different directories, it is important to understand these directories, especially for the Linux beginners, because the Linux itself directory structure is more complex, If you don't know the MySQL installation directory then you can't talk about deep learning.

Here are a few of the directories to look at.

1. Database Directory
/var/lib/mysql/

2. Configuration files
/usr/share/mysql (mysql.server command and configuration file)

3. Related commands
/usr/bin (Mysqladmin mysqldump and other commands)

4. Startup script
/etc/rc.d/init.d/(startup script file for MySQL directory)

Eight, change the login password
MySQL does not have a password by default, the importance of increasing the password is self-evident.

1, the command (we just installed successfully, the console also tells us the following command to change the password)
/usr/bin/mysqladmin-u root password ' new-password '
Format: Mysqladmin-u username-P Old password password new password

2. Example
Example 1: Add a password to root 123456.
Type the following command:
[Email protected] local]#/usr/bin/mysqladmin-u root password 123456
Note: Because Root does not have a password at the beginning, the-p old password can be omitted.

3. Test whether the modification is successful
1) Login without password
[[email protected] local]# MySQL
ERROR 1045:access denied for user: ' [email protected] ' (Using password:no)
An error is displayed stating that the password has been modified.
2) Log in with the modified password
[Email protected] local]# mysql-u root-p
Enter Password: (Enter the modified password 123456)
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>
Success!
This is done by changing the password with the Mysqladmin command, or by modifying the library.

Nine, start and stop

Execute command: Service MySQL status View MySQL boot status

1. Start
After the MySQL installation is complete, start the file MySQL in the/ETC/INIT.D directory and run the following command when it needs to start.
[[email protected] 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. Auto-start
1) See if MySQL is in the auto-start list
[Email protected] local]#/sbin/chkconfig--list
2) Add MySQL to your system's start-up service group
[[email protected] local]#/sbin/chkconfig--add MySQL
3) Remove MySQL from the Startup service group.
[[email protected] local]#/sbin/chkconfig--del MySQL

X. Change the MySQL directory

The default data file storage directory for MySQL is/var/lib/mysql. The following steps are required if you want to move the directory to/home/data:

1. Set up the data directory in the home directory
Cd/home
mkdir data

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

3. Move/var/lib/mysql Entire directory to/home/data
mv/var/lib/mysql/home/data/
This will move the MySQL data file to/home/data/mysql.

4. Locate the MY.CNF configuration file
If there is no MY.CNF configuration file under the/etc/directory, locate the *.cnf file under/usr/share/mysql/, and copy one of them to/etc/and rename it to MY.CNF). The command is as follows:
[Email protected] 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 correctly, you need to indicate where the Mysql.sock file is generated. Modify the value in the Socket=/var/lib/mysql/mysql.sock line to the right of the equals sign:/home/mysql/mysql.sock. The operation is as follows:
VI my.cnf (Edit the My.cnf file with VI tool, find the following data to modify)
# The MySQL server
[Mysqld]
Port = 3306
#socket =/var/lib/mysql/mysql.sock (original content, in order to be more secure with "#" Comment this line)
Socket =/home/data/mysql/mysql.sock (plus this line)

6. Modify MySQL startup script/etc/rc.d/init.d/mysql
Finally, the MySQL startup script needs to be modified/etc/rc.d/init.d/mysql, the path to the right of the equal sign in the Datadir=/var/lib/mysql line is changed to your current actual storage path: Home/data/mysql.
[Email protected] etc]# Vi/etc/rc.d/init.d/mysql
#datadir =/var/lib/mysql (Note this line)
Datadir=/home/data/mysql (plus this line)

7. Restart MySQL Service
/etc/rc.d/init.d/mysql start
or restart Linux with the reboot command
If the work is moving properly, otherwise check the previous 7 steps.

Xi. adding MySQL Users

Format: Grant Select on database. * To User name @ login host identified by "password"
Example 1, add a user user_1 password of 123, so that he can log on any host, and all databases have query, insert, modify, delete permissions. First, use the root user to connect to MySQL, and then type the following command:

Mysql> Grant Select,insert,update,delete on * * to [e-mail protected] "%" identified by "123";
Example 1 added user is very dangerous, if you know the user_1 password, then he can be on any computer on the Internet to log on to your MySQL database and your data to do whatever you like, the solution is shown in Example 2.

Example 2, the addition of a user user_2 password of 123, so that the user can only log on localhost, and the database AAA can query, insert, modify, delete operations (localhost refers to the local host, that is, the MySQL database host), This allows the user to use the password that knows user_2, and he cannot access the database directly from the Internet, only through the MySQL host to operate the AAA library.

Mysql>grant Select,insert,update,delete on aaa.* to [e-mail protected] identified by "123";

With the new user if you can't log in MySQL, log in with the following command:

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

12. Backup and Recovery

1. Backup

For example, to back up the AAA library created in the example above to file Back_aaa

[[email protected] root]# Cd/home/data/mysql (go to the library directory, this example library has been transferred from Val/lib/mysql to/home/data/mysql, see "Change MySQL directory" above)
[Email protected] mysql]# mysqldump-u root-p--opt aaa > BACK_AAA

2. Recovery

[Email protected] mysql]# mysql-u root-p CCC < BACK_AAA

13. Common operation commands of MySQL database

Note: Each command in MySQL is followed by a semicolon;

1. Display Database
mysql> show databases;
+----------+
| Database |
+----------+
| MySQL |
| Test |
+----------+
2 rows in Set (0.04 sec)
MySQL has just finished installing a database of two databases: MySQL and test. MySQL library is very important, it has the MySQL system information, we change the password and new users, in fact, the library with the relevant tables to operate.

2. Display the tables in the database
mysql> use MySQL; (Open library, operate on each library to open this library, 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 structure of the data table:
describe table name;

4. Display the records in the table:
SELECT * from table name;
Example: Displays the records in the user table in the MySQL library. All users who can operate on the MySQL user are in this table.
Select * from user;

5, build the library:
Create database name;
For example: Create a library with a name-bit AAA
mysql> Create databases AAA;

6, build the table:
Use library name;
CREATE TABLE table name (field settings list);
For example: Create a table in the AAA library that you just created name, the table has ID (ordinal, autogrow), XM (name), XB (gender), CSNY (birth date) four fields
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 table structure you just created.
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 record
For example: Add a few related records.
mysql> INSERT into name values (' ', ' Zhang San ', ' Male ', ' 1971-10-01 ');
mysql> INSERT into name values (' ', ' white Clouds ', ' female ', ' 1972-05-20 ');
You can use the Select command to verify the results.
Mysql> select * from name;
+----+------+------+------------+
| ID | XM | XB | CSNY |
+----+------+------+------------+
| 1 | Zhang San | Male | 1971-10-01 |
| 2 | Baiyun | Women | 1972-05-20 |
+----+------+------+------------+

8. Change of record
For example: Change Zhang San's birth date to 1971-01-10
Mysql> Update name set csny= ' 1971-01-10 ' where xm= ' Zhang San ';

9. Delete records
For example: Delete the Zhang San record.
mysql> Delete from name where xm= ' Zhang San ';

10. Deleting the library and deleting the table
drop database name;
drop table name;

--------------------------------------------------------------------------------------------------------------- ----------------------------
~ ~ Installation Complete ~ ~
--------------------------------------------------------------------------------------------------------------- ----------------------------

Problem Solving:
First, the database installation is complete, we need to connect remotely, error: 1130-host ' xxx.xxx.xxx.xx ' is not allowed to connect to this MySQL serverconnection closed b Y foreign host.
Error Number: 1130
Problem Analysis: MySQL does not have permission to open Telnet.
Workaround:
Please verify that you have completed the correct database server, and authorize your Web server to connect, do the following:

There are two major strides in opening MySQL's remote login account:

1. Determine that the firewall on the server does not block port 3306.

MySQL default port is 3306, need to determine the firewall does not block 3306 port, otherwise remote is unable to connect via 3306 port to MySQL.

If you specified a different port when you installed MySQL, open the port number that you specified for MySQL in the firewall.

If you don't know how to set up a firewall on your server, check with your server administrator.

2, add to allow remote connection to MySQL users and authorized.

1) First log in to MySQL with the root account

In the Linux host, enter the following command at the command prompt line.

[Email protected] ~]# mysql-u root-p
Enter Password: (enter password)

2) Create a remote login user and authorize
Command: Grant all privileges the testdb.* to [e-mail protected] ' 123.123.123.123 ' identified by ' 123456 ';

The above statement indicates that all permissions to the TestDB database are authorized to shanhy this user, allowing Shanhy users to remotely log in 123.123.123.123 this IP, and set the Shanhy user's password to 123456.

All parameters are analyzed below:

All privileges means giving all permissions to the specified user, which can also be replaced by assigning a specific permission, such as: Select,insert,update,delete,create,drop, and so on, with the "," half-width comma separated by the specific permissions.

Testdb.* means that the above permissions are for which table, TestDB refers to the database, followed by the * for all tables, it can be inferred that: for all of the database is authorized as "*. *" For all tables, the entire table for a database is authorized as "database name. *", A table for a database is authorized as the database name. Table name.

Shanhy indicates which user you want to authorize, the user can be a user who exists, or it can be a non-existent user.

123.123.123.123 represents the IP address that allows remote connections, or "%" if you want to limit the IP of the link.

123456 is the user's password.

Use: Grant all privileges on * * to [email protected] '% ' with GRANT option; Give any host access to data

After executing the above statement, execute the following statement before it can take effect immediately.

Flush privileges;

Operate using the above authorization method or try the following table change method:
1. mysql>use MySQL;
2. Mysql>update User Set host = '% ' where user = ' root ';
3. Mysql>select host, user from user;

second, modify the MySQL configuration file, adjust the database encoding and table name casing problems
1. Modify the configuration
increase under [client]
Default-character-set=utf8

increase under [mysqld]
init-connect= ' SET NAMES UTF8 '
Character-set-server=utf8
Collation-server=utf8_general_ci
# (Note that Linux is the default after MySQL is installed: Differentiate the case of the table name, not the case of the column name; lower_case_table_names = 0 0: Case-sensitive, 1: not case-sensitive)
Lower_case_table_names=1
# (Set the maximum number of connections, default is the maximum number of connections allowed for the 151,MYSQL server 16384)
max_connections=1000

Added under [MySQL]
Default-character-set=utf8

PS: Here there is no deep, important point is to pay attention to the version, there are a lot of changes on the web, are to be in [mysqld] under the addition of Default-character-set=utf8, in fact, 5.0 (>) version above is not supported, if you change this way,  Your MySQL will not be able to start, reported a mistake, the message is "The last time you quit without saving the PID?" (This is probably the wrong hint.)
or add parameter at startup: Mysqld--default-character-set=utf8
or add parameters at compile time:./configure--width-charset=utf8


2, Save (if you use VI to operate, the save will be error, because this file is a read-only property, you can use wq! Force save exit). Restart MySQL.

Three. Modify the MY.CNF configuration file, set the default engine to InnoDB, and add the following two sentences below [mysqld].

Default-storage-engine = InnoDB
Default_table_type = InnoDB

Also open the following:

Innodb_data_home_dir =/var/lib/mysql/
Innodb_data_file_path = Ibdata1:10m: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 = 16M
Innodb_additional_mem_pool_size = 2M
# Set: _log_file_size to% of buffer pool size
Innodb_log_file_size = 5M
Innodb_log_buffer_size = 8M
Innodb_flush_log_at_trx_commit = 1
Innodb_lock_wait_timeout = 50

Four. Modify the maximum number of connections, default is 151, change to 500

#vi/etc/my.conf

[Mysqld]

max_connections=500

Verifying the configuration
Show variables like ' max_con_% ';

Five. mysql cannot create a function problem resolution

The following is a reference fragment:

Error code:1418

This function has none of the deterministic, NO SQL, or READS SQL DATA in its declaration and binary logging are enabled (you * Might* want to use the less safe log_bin_trust_function_creators variable)

(0 ms taken)

Here's how to fix it:

Add a line of Log-bin-trust-function-creators=1 to the [mysqld] tag in My.ini (Linux my.cnf) file

Restart the MySQL service to

Six. MySQL 1045 error

The following is a reference slice:
Enter MySQL command line on the MySQL server
Execute UPDATE user SET Password=password (' NewPassword ') where user= ' root '
Execute FLUSH privileges;
Then service MySQL Restart restart the MySQL services.

Seven. CentOS Installation MySQL conflict resolution method

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

Workaround:

Uninstalling Mysql:rpm-e--nodeps mysql-libs-5.1.*

Eight. mysql 5.6 does not log on after installation

Error:you must SET PASSWORD before executing this statement
Processing method:
The default root password is empty, the landlord directly into the MySQL, and then go in to change Ah, for example:

mysql> SELECT 1;
ERROR 1820 (HY000): Must SET PASSWORD before executing this statement

mysql> SET PASSWORD = PASSWORD (' New_password ');
Query OK, 0 rows affected (0.01 sec)


Ix. mysql5.6 View the default password

[Email protected] ~]# more. Mysql_secret
Or
[Email protected] ~]# More/root/.mysql_secret
# The random password set for the root user at Wed June 18:23:34 (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 = 32M
When a MySQL client or mysqld server receives packets that are larger than max_allowed_packet bytes, it issues a "packet too large" error and closes the connection. For some clients, if the communication packet is too large, you may encounter a "missing connection to the MySQL server" error during query execution.

Both the client and the server have their own max_allowed_packet variables, so if you intend to handle large packets, you must increase the variables on both the client and the server. In general, the server default max-allowed-packet is 1MB

Xi. MySQL 1030 appears Got error storage engine solution

Error Description:
Error 1030 (HY000): Got error from storage engine

Cause of Error:
There is not enough temporary space to execute this SQL statement


Workaround:
Point the Tmpdir to a directory with a large hard disk space
1, modify the MY.CNF configuration file, add or modify Tmpdir = Temporary file directory location under DataDir
2. Modify/etc/rc.d/init.d/mysql startup file, add or modify Tmpdir = Temp file directory location
Temporary files generally we put under the datadir, such as: Tmpdir =/home/data/mysql_data/tmp

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

Linux installation MySQL and some common problems solutions