linux下安装mysql及用户、引擎、连接数、编码等相关设置

Mysql版本：5.1.41

 

安装过程：

 

1. 安装Server：

 

# rpm -ivh MySQL-server-community-5.1.41-0.rhel5.i386.rpm

安装后路径

数据库目录：/var/lib/mysql/

配置文件：/usr/share/mysql

相关命令：/usr/bin

启动脚本：/etc/rc.d/init.d/

 

2. 安装Client：

 

# rpm -ivh MySQL-client-community-5.1.41-0.rhel5.i386.rpm

 

3. 默认会添加自启动程序并启动Mysql，查看Mysql是否已经启动。

 

# netstat -nat

 

4. 修改root账号密码。

 

mysql>update mysql.user set password=PASSWORD('newpassword') where User='root';

 

5. 增加root账号远程访问权限

 

mysql>grant all on *.* to " Identified by "root123456";

 

6. 增加非root用户，用户远程访问。

 

mysql>grant select,insert,update,delete on mydata.* to test" Identified by "test123456";

 

7. 修改my.cnf配置文件，修改mysql编码为gbk

 

# cp /usr/share/mysql/my-medium.cnf /etc/my.cnf

# vi /etc/my.cnf

修改下面的内容：

[client]
default-character-set=gbk

[mysqld]
default-character-set=gbk

[mysql.server]
default-character-set=gbk

[mysqld_safe]
default-character-set=gbk

[mysql]
default-character-set=gbk

 

8. 修改my.cnf配置文件，设置默认引擎为InnoDB，在[mysqld]下面最后增加下面两句。

 

default-storage-engine = InnoDB
default_table_type = InnoDB

 

同时打开下面的内容：

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 25 % 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

 

9. 修改最大连接数，默认是151 ，修改为500

 

#vi /etc/my.conf

[mysqld]

max_connections=500

 

10. 重启mysql

 

#service mysql restart

 

11. 查看设置是否生效。

 

mysql> show variables like 'character/_set/_%';

mysql> show variables like 'max_con_%';

 

12. 相关命令

 

mysql> show databases;

mysql> use datebase;

mysql> show tables;

mysql> desc table_name;

mysql> create database db;

mysql> drop database db;

mysql> create table table_name...;

mysql> drop table table_name;

mysql> insert into name values...;

mysql> update name set...;

mysql> delete from name where...;

mysql> alter table survyssrs MODIFY supportAvaiScore varchar(10)...;

mysql> update name set...;

mysql> mysqldump -u root -p db > db_20091125.sql(备份数据库)

mysql> mysql -u root -p db < db_20091125.sql(还原数据库)

mysql> mysqldump -u root -p -h 192.168.1.105  db table >/home/table.sql(备份表)

mysql> mysql -u root -p db < table.sql(还原表)

 

13. 忘记root密码的操作。

 

a. 关闭mysql

b. /usr/bin/mysqld_safe --skip-grant-tables &

c. update mysql.user set password=PASSWORD('newpassword') where User='root';