创建数据库服务

创建数据库服务：

建议跟MySQL服务相关的操作(数据库服务启动、关闭、维护等)均在mysql用户下执行

1.创建目录

#mkdir /data

#cd /data/

#mkdir –p /data/mysqldata/{3306/{data,tmp,binlog},backup,scripts}

#chown –R mysql:mysql mysqldata

#su – mysql

$cd /usr/local/mysql

 

2.编辑参数文件

$vim /data/mysqldata/3306/my.cnf

[client]

port = 3306

socket = /data/mysqldata/3306/mysql.sock

 

#The MySQL server

[mysqld]

port = 3306

user = mysql

socket = /data/mysqldata/3306/mysql.sock

pid-file = /data/mysqldata/3306/mysql.pid

basedir = /usr/local/mysql

datadir = /data/mysqldata/3306/data

tmpdir = /data/mysqldata/3306/tmp

open_files_limit = 10240

explicit_defaults_for_timestamp

sql_mode = NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

 

#Buffer

max_allowed_packet = 256M

max_heap_table_size = 256M

net_buffer_length = 8K

sort_buffer_size = 2M

join_buffer_size = 4M

read_buffer_size = 2M

read_rnd_buffer_size = 16M

 

#Log

log-bin = /data/mysqldata/3306/binlog/mysql-bin

binlog_cache_size = 32M

max_binlog_cache_size = 512M

max_binlog_size = 512M

binlog_format = mixed

log_output = FILE

log-error = /data/mysqldata/3306/mysql-error.log

slow_query_log = 1

slow_query_log_file = /data/mysqldata/3306/slow_query.log

general_log = 0

general_log_file = /data/mysqldata/3306/general_query.log

expire-logs-days = 14

 

#InnoDB

innodb_data_file_path = ibdata1:2048M:autoextend

innodb_log_file_size = 256M

innodb_log_files_in_group = 3

innodb_buffer_pool_size = 1024M

 

[mysql]

no-auto-rehash

prompt = (\u@\h) [\d]>\_

default-character-set = gbk

 

初始化MySQL数据库：

注意修改/etc/hosts文件

位于MySQL软件安装目录下的scripts目录内mysql_install_db

$/usr/local/mysql/scripts/mysql_install_db --datadir=/data/mysqldata/3306/data

--basedir=/usr/local/mysql --user=mysql

 

 

启动数据库服务器：

配置MySQL服务自启动(support-files/mysql.server)，不建议使用

$mysqld_safe --defaults-file=/data/mysqldata/3306/my.cnf &

mysqld_safe命令行方式才是Linux/UNIX平台下被推荐的MySQL服务启动方式

检查3306端口，检查mysqld相关进程

$netstat –tlnp | grep 3306

$ps –ef | grep mysql | grep –v grep

此时MySQL数据库可以被mysql客户端连接访问

 

配置MySQL数据库：

MySQL数据库创建之后自动创建一个名为root的系统账户，拥有最大权限

进入mysql，默认情况下root用户是不设密码的

$mysql

(root@localhost) [(none)]> select user,host from mysql.user;

 

直接到数据库中操作与用户相关的字典表

保留一条允许root用户从本地连接的记录外，其他用户全部删除

(root@localhost) [(none)]> delete from mysql.user where (user,host) not in (select 'root','localhost');

 

修改用户名和密码

(root@localhost) [(none)]> update mysql.user set user='system',password=password('123456');

 

消除对test库的权限隐患

(root@localhost) [(none)]> truncate table mysql.db;

 

使操作生效

(root@localhost) [(none)]> flush privileges;

 

重新登录

 

[mysql@mysql1 ~]$ mysql -usystem -p123456