Learn a little more (vi)--install config MySQL under Linux

1. Download and install MySQL :

[[Email protected]~]# cd/usr/local/src

[Email protected]]# wget http://syslab.comsenz.com/downloads/linux/mysql-5.1.40-linux-i686-icc-glibc23.tar.gz

[Email protected]]# tar-zxvf mysql-5.1.40-linux-i686-icc-glibc23.tar.gz

[[Email protected]]# MV Mysql-5.1.40-linux-i686-icc-glibc23/opt/mysql

To create a mysql user shell for /sbin/nologin :

[[Email protected]]# useradd-s/sbin/nologin MySQL

[Email protected]]# mkdir-p/data/mysql

[Email protected]]# chown-r Mysql:mysql/data/mysql

[[Email protected]]# cd/opt/mysql

[[Email protected]]#./scripts/mysql_install_db--user=mysql--datadir=/data/mysql

Note: in this step, the following error message may be reported--

Installing MySQL system Tables..../bin/mysqld:error while loading shared libraries:libaio.so.1 ...

This is due to the lack of a Libaio library that causes mysql_install_db to fail, as long as the Yum command is installed to execute the mysql_install_db again :

[Email protected]]# yum install-y Libaio

[Email protected]]# CP support-files/my-large.cnf/etc/my.cnf

[Email protected]]# CP support-files/mysql.server/etc/init.d/mysqld

[[Email protected]]# chmod 755!$

[Email protected]]# vim!$

Modify two of these lines, the original content is as follows:

Basedir=

Datadir=

The following changes are included:

Basedir=/opt/mysql

Datadir=/data/mysql

Save exit. WhereBasedir is set to the MySQL installation directory, for MySQL , its default installation directory is /usr/local/mysql , if it has been installed in the directory, You do not need to deliberately set basedir , otherwise, as in this example, MySQL is installed under/opt/mysql,basedir is set to/opt/mysql . Similarly, set DataDir to the corresponding/data/mysql in the previous mysql_install_db .

Add mysqld to the service:

[[email protected] MySQL] #chkconfig--add mysqld

[[Email protected]]# chkconfig mysqld on

Start MySQL :

[[email protected] MySQL] #service mysqld start

The following information appears to represent MySQL installation Completed, has started:

Starting mysql.success!

Finally, modify the/etc/profile file to add the bin/directory under MySQL to the PATH :

[[Email protected]]# vim/etc/profile

Exportpath= $PATH:/opt/mysql/bin

[Email protected]]# Source!$

in this way, we are using The MySQL command eliminates the need to use absolute paths.

2. Configure MySQL :

Take a position, and then add this part of the content, so please look forward to it.

3. MySQL common operation:

3.1 Initial use:

by default, The root user of MySQL does not have a password, and the first thing we do before we use MySQL is to set the password for the root User:

[Email protected] ~]# mysqladmin-u rootpassword ' newpasswd '

after Setup is complete, connect MySQL database ( You can omit - u root when logging in with MySQL root):

[Email protected] ~]# mysql-uroot-p// equivalent to mysql-p

Enter Password:

Enter the password you just set to log in to the root user, you can do the following common actions:

See which libraries are availableshow databases;
View a table for a libraryUse DB; Show tables;
view the fields of a tableDesc TB;
View the Build Table statementShow CREATE table TB;
which user is currentlySelect User ();
Current LibrarySelect Database ();
Create a libraryCreate Database db1;
Create a tableCREATE TABLE T1 (' id ' int (4), ' name ' char (40));
View Database VersionSelect version ();
ViewMysqlStatusShow status;
ModifyMysqlParametersShowvariables like ' max_connect% '; Set global max_connect_errors = 1000;
ViewMysqlQueueShowprocesslist;
create a regular user and authorizeGrant all on * user1 identified by ' 123456 ';
Grant all on db1.* to ' user2 ' @ ' 10.0.2.100 ' identified by ' 111222 ';
Grant all on db1.* to ' User3 ' @ ' percent ' identified by ' 231222 ';
Change PasswordUpdate Mysql.user Set Password=password ("Newpwd") where user= ' username '; Flush privileges;
EnquirySelect COUNT (*) Frommysql.user; SELECT * from Mysql.db; SELECT * from mysql.db where host like ' 10.0.% ';
InsertUpdate db1.t1 set name= ' AAA ' where id=1;
Clear TableTRUNCATE TABLE db1.t1;
Delete a tabledrop table db1.t1;
Deleting a databaseDrop database db1;
Repair TableRepair table tb1 [use frm];

3.2 Backup and recovery:

Use the following command to make a database backup:

[Email protected] ~]# mysqldump-uroot-pdbname > Db_bak.sql

Use the following command for database recovery:

[Email protected] ~]# Mysql-uroot-p dbname < Db_bak.sql

the prerequisite for recovery is that the dbname library exists in the database, or the dbname library needs to be created first .

In addition to backing up the entire database at one time, you can back up only one table in a single database at a time, using the following command:

[Email protected] ~]# mysqldump-uroot-pdbname tabelname > Tb_bak.sql

When you perform a database recovery, the same as when you back up the entire database:

[Email protected] ~]# Mysql-uroot-p dbname < Tb_bak.sql

The premise of recovery is also that the dbname library exists in the database, and the TableName table does not necessarily exist.

By default, this backup is with data from the original database/ table, and if you only want to back up the table structure , just add the-D parameter:

[Email protected] ~]# mysqldump-uroot-p-ddbname > Db_structure_bak.sql

to avoid garbled characters that may be caused by different character sets when backing up/restoring, you can specify a character set on backup/ restore, using the--default-character-set parameter:

[Email protected] ~]# Mysqldump-uroot-p--default-character-set=utf-8 dbname > Db_bak.sql

[Email protected] ~]# Mysql-uroot-p--default-character-set=utf-8 dbname < Db_bak.sql

3.3 Forget MySQL The password:

To modify the/etc/my.cnf file:

[Email protected] ~]# VIM/ETC/MY.CNF

Add the following content to the [mysqld] section:

Skip-grant

Save exit, restart MySQL :

[Email protected] ~]# service mysqld restart

Shutting down MySQL. success!

Starting MySQL. success!

you can then go directly to the password-Free State MySQL :

[[email protected] ~]# mysql-uroot// equivalent to MySQL

mysql> use Mysql;update user u setu.password=password (' NewPassword ') from user u where u.user= ' root ';

Query OK, 3 Rows Affected (0.00 sec)

Rows Matched:3 Changed:3 warnings:0

after modifying the/etc/my.cnf file again, Remove or comment out the added skip-grant option, and restart MySQL again :

[Email protected] ~]# service mysqld restart

Add: If you want to change to a new password if you know your existing password, you do not need to enter to reset the password by executing the UPDATE statement in the MySQL database, use the mysqladmin command:

[Email protected] ~]# Mysqladmin-uroot-ppassword ' newpasswd '

This article is from the "barrel of fake dog excrement" blog, please be sure to keep this source http://xitongjiagoushi.blog.51cto.com/9975742/1627864

Learn a little more (vi)--install config MySQL under Linux