MySQL configuration in Ubuntu

1. Download the MySQL Installation File
The following two files are required to install MYSQL:
MySQL-server-4.0.23-0.i386.rpm
MySQL-client-4.0.23-0.i386.rpm
To: http://www.mysql.com/downloads/mysql-4.0.html, open this webpage and pull down the webpage to the latest Linux
X86 RPM downloads, find "server" and "client programs", and download the above two RPM files.
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 for this command installation is-IVH.
, Where I indicates that the specified RMP package will be installed, V indicates the detailed information during installation, and h indicates that the "#" symbol appears during 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-4.0.23-0.i386.rpm
The following information is displayed.
Warning: MySQL-client-4.0.23-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-4.0.23-0.i386.rpm
Warning: MySQL-client-4.0.23-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
Server is installed in a directory by default. Its database files, configuration files, and command files are in different directories. It is very important to understand these directories, especially for Linux beginners, because
The directory structure of Linux itself is complicated. If you do not know the installation directory of MySQL, you will not 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:/usr/lib/MySQL/Data
1. Create a data directory under the Home Directory
CD/home
Mkdir data
2. Stop the MySQL service process:
/Usr/bin/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 dfg
Mysql> create databases dfg;
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 dfg database, the table has four fields: ID (serial number, auto-increment), XM (name), Xb (gender), and csny (date of birth ).
Use dfg;
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 and query, insert, modify, and delete the database dfg.
(Localhost refers to the local host, that is, the host where the MySQL database is located.) in this way, the user knows the password of user_2 and cannot directly access the database from the Internet.
MySQL host to operate the dfg library.
Mysql> grant select, insert, update, delete on dfg. * 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 dfg library created in the previous example to the file back_dfg.
[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 dfg> back_dfg
2. Recovery
[Root @ test MySQL] # mysql-u root-p ccc <back_dfg

++ ++
++ ++

To install MySql in Linux, run the following command at a terminal prompt:

Sudo apt-Get install mysql-Server
Sudo apt-Get install mysql-Client
Sudo apt-Get install php5-mysql
// Installing the php5-mysql is to connect PHP and MySQL

Once the installation is complete, the MySQL server should be started automatically.

You can run the following command at a terminal prompt to check whether the MySQL server is running:
Sudo netstat-tap | grep MySQL
When you run this command, you can see a line similar to the following:
TCP 0 0 localhost. localdomain: MySQL *: * listen-

If the server cannot run properly, run the following command to start it:
Sudo/etc/init. d/MySQL start MySQL

Run the following command to view the current MySQL status.
Sudo service MySQL status

Go to MySQL
$ Mysql-uroot-P administrator password

Configure the MySQL administrator password:
Sudo mysqladmin-u Root Password newpassword

Install the MySQL administrator Gui
Search for MySQL under the new software
Mysql-Admin package. After installation, you can run it in applications/programming.
You can also install the MySQL graphical management tool sudo apt-Get mysql-Admin mysql-query-browser.

++ ++
Configure MySQL to support access from other clients.
Log on to the MySQL server and execute the add user operation under the MySQL database:
Format: grant select on database. * To username @ login host identified by "password"

Grant all on dbname. * To test @ '%' identified by '20140901'
This command is used to create a user test and grant it unrestricted permissions to access the database dbname, which can be accessed on any machine.

Grant all on *. * To 'remote' @ '192. 16.21.39 'identified by 'Password ';
If you want to set any client to be able to be connected as root, you can write as follows:
Grant all on *. * To 'root' @ '%' identifiied by 'root password'

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;
Second, or use the following:
Mysql> Update user set host = "%" where host = "192.168.1.1 ";

This method is as follows:
1. Change the table. It may be that your account is not allowed to log on remotely, but only on localhost. At this time, you only need to log in to MySQL on the computer of localhost, and change the "host" entry in the "user" table in the "MySQL" database to "%" from "localhost"
Mysql-u root-pvmwaremysql> use MySQL; mysql> Update user set
Host = '%' where user = 'root'; mysql> select host, user from user;
2. Authorization method. For example, if you want myuser to use mypassword to connect to the MySQL server from any host.
Grant all privileges on *. * To 'myuser' @ '%' identified by 'mypassword' with grant option;
If you want to allow myuser to connect to the MySQL server from a host whose IP address is 192.168.1.3, and use mypassword as the password
Grant all privileges on *. * To 'myuser' @ '192. 168.1.3 'identified by 'mypassword' with grant option;

But the problem persists:

Find the command mysql> flush privileges // make the modification take effect

Zero rows are affected.

The problem persists. Restart MySQL.
Sudo/etc/init. d/MySQL restart
Or not.

I learned that MySQL has a local binding and found the problem.
Search for files
Skip-networking
Change
# Skip-networking
Save and close the file.
Edit/etc/MySQL/My. CNF
Sudo gedit/etc/MySQL/My. CNF

# Instead of skip-networking the default is now to listen only on
# Localhost which is more compatible and is not less secure.
Bind-address = 127.0.0.1

Annotate "bind-address = 127.0.0.1"
Sudo/etc/init. d/MySQL restart
Or
Service mysqld restart
Remote access after restart

MySQL uninstall:
Apt-Get autoremove mysql-Server
Apt-Get autoremove mysql-Client

First try
Apt-Get -- reinstall install mysql-server ???
If it is not running and MySQL is rarely associated, You Can apt-Get remove -- purge
Mysql-server ??? Then install the package. If many associations cannot be deleted, find the corresponding package in dpkg-S/etc/init. d/MySQL, Apt-get-d
-- Reinstall install mysql-server ??? Then, unbind the corresponding Deb and copy MySQL.
Dpkg-x/var/Cache/APT/archives/MySQL-server ???. DEB/tmp/foo
CP/tmp/Foo/etc/init. d/MySQL/etc/init. d
If not, the ultimate solution is to delete, delete, and reinstall the corresponding packages under/var/lib/dpkg/info without affecting the system.
++ ++
Modify the default Character Set of the database and solve Chinese garbled characters of phpMyAdmin and MYSQL:
After the installation, the database encoding is Latin1 by default. This encoding will cause garbled characters when storing Chinese characters. Therefore, you must change the character set of the database to a character set that supports Chinese characters during use, the following uses UTF-8 as an example to briefly record the operations required to modify the character set:
1) After logging on to the database, run the show variables like 'character % 'command to view the usage of the character set of the database. below is the MySQL Character Set setting before the modification:
+ -------------------------- + ---------------------------- +
| Variable_name | value |
+ -------------------------- + ---------------------------- +
| Character_set_client | Latin1 |
| Character_set_connection | Latin1 |
| Character_set_database | Latin1 |
| Character_set_filesystem | binary |
| Character_set_results | Latin1 |
| Character_set_server | Latin1 |
| Character_set_system | utf8 |
| Character_sets_dir |/usr/share/MySQL/charsets/|

2) to replace the character set with UTF-8, we only need to modify the configuration file my. CNF under/etc/MySQL.
First stop the MySQL service [Sudo/etc/init. d/MySQL stop], and then add the following configuration segment to my. CNF:

[Client]
Default-character-set = utf8
[Mysqld_safe]
Default-character-set = utf8
[Mysqld]
Default-character-set = utf8
[MySQL]
Default-character-set = utf8

3) Complete the preceding modification, save the modification, and restart MySQL.
[Sudo service MySQL restart]
After logging on to MySQL again, execute: Show variables like 'character % '; the character set should be changed to utf8
For phpMyAdmin connection verification, select utf8_general_ci by default. In this way, Chinese garbled characters are configured, and you can also use phpMyAdmin to manage the MySQL database.
++ ++

MySQL installation:
Sudo apt-Get install mysql-Server
Sudo apt-Get install mysql-Client