MySQL knowledge Summary (1) installation and configuration (Linux CentOS), mysqlcentos

MySQL knowledge Summary (1) installation and configuration (Linux CentOS), mysqlcentos
1 Installation

Environment CentOS

Yum install-y mysql-server mysql-deve

Service Startup

1.1 start

Service mysqld start

1.2 restart

Service mysqld restart

Stop 1.3

Service mysqld stop

1.4 start and Close processes

View Processes

Ps aux | grep mysql

Kill Process

Kill-9 xx

Cd/usr/local/mysql/bin/

Start command:

./Mysqld_safe -- user = mysql &

View startup status

Netstat-ntlp | grep mysql

1.5 uninstall

Yum remove mysql-server mysql-libs compat-mysql51

Rm-rf/var/lib/mysql

Rm/etc/my. cnf

Check whether there is any mysql software:

Rpm-qa | grep mysql

If yes, continue to delete

2. log on to the database 2.1 and log on to the local database:

Mysql-u root-p and enter the password.

Exit: exit

View system users

Vi/etc/mysql/debian. cnf

2.2 log on to the remote database:

Mysql-h [host]-u [user]-p [password]-P [port]-D [dbName]

Mysql-h192.168.1.225-utest-ptest-P8066-Ddbtest

3. Solve the Problem of remote database access

1) run the command: netstat-an | grep [mysqlport] to check the ip address limit of the mysql running port.

If it is localhost, it indicates that mysql is bound with an access ip address and needs to be modified.

Vi/etc/mysql/my. cnf

Under [mysqld] (for vi editor usage, refer to linux operation manual)

Bind-address = 127.0.0.1. Just comment it out.

Restart mysql

2) log on to mysql using root to execute

Use mysql

Select user, host from user;

The result is as follows:

+ ------------------ + --------------- +

| User | host |

+ ------------------ + --------------- +

| Debian-sys-maint | localhost |

| Root | localhost |

+ ------------------ + --------------- +

On the surface, only localhost can access

Execute the following statement:

Grant all privileges on *. * TO 'root' @ '%' identified by 'Bruce 'with grant option;

Select user, host from user;

The result is as follows:

+ ------------------ + ----------- +

| User | host |

+ ------------------ + ----------- +

| Root | % |

| Debian-sys-maint | localhost |

| Root | localhost |

+ ------------------ + ----------- +

So far, the problem of remote access to mysql has been solved.

4. Table name Case sensitivity

Vi/etc/mysql/my. cnf

Add under [mysqld]

Lower_case_table_names = 1

Must be set before creating a table

Reference: http://blog.163.com/zjc_8886/blog/static/240817520117111562089/

5 solve coding problems

Vi/etc/mysql/my. cnf

Add under [client]

Default-character-set = utf8

Added under [mysqld]

Character_set_server = utf8

Added under [mysql]

Character_set_server = utf8

View and modify the maximum database connection

View the maximum database connection

Show VARIABLES like '% connection %'

6. Modify the maximum database connection.

Ubuntu:

Vi/etc/mysql/my. cnf

Centos:

Vi/etc/my. cnf

Add

Max_connections = 200

Restart mysql

View the number of Running Processes

Show processlist