MySQL参数优化及基础操作

系统环境：Centos-6.7

安装软件：mariadb10.0.21

安装机器：192.168.4.251

软件安装位置：/usr/local/mysql/

数据存放位置：/data/mydata/

首先优化数据库参数：

#vi /etc/my.cnf 

[client]

port= 3306

socket= /data/mydata/mariadb.sock

[mysqld]

port= 3306

socket= /data/mydata/mariadb.sock

skip-external-locking

####常用设置####

##slow log

slow-query-log = 1

slow-query-log-file = /data/mydata/mysql-slow.log

long_query_time = 3

log_queries_not_using_indexes=1

##最大连接数

max_connect_errors = 50000

max_connections = 1000

##加快网络解析

skip_name_resolve

##bin log

log-bin=mysql-bin

binlog_format=mixed

expire_logs_days=7

max_binlog_size = 512M

max_binlog_cache_size = 2G

##MyIASM引擎参数

key_buffer_size = 12G

max_allowed_packet = 64M

table_open_cache = 64

sort_buffer_size = 512K

net_buffer_length = 8K

read_buffer_size = 256K

read_rnd_buffer_size = 512K

myisam_sort_buffer_size = 8M

##Innodb引擎参数

innodb_data_home_dir = /data/mydata

innodb_data_file_path = ibdata1:2G;ibdata2:2G:autoextend

innodb_log_group_home_dir = /data/mydata

innodb_flush_method = O_DIRECT

innodb_file_per_table = 1

innodb_open_files=60000

innodb_flush_log_at_trx_commit = 1

innodb_lock_wait_timeout = 100

innodb_additional_mem_pool_size = 32M

innodb_buffer_pool_size = 48G

innodb_log_buffer_size= 400M

innodb_log_file_size = 128M

innodb_log_files_in_group = 4

innodb_file_io_threads = 8

innodb_write_io_threads =8

innodb_io_capacity=400

innodb_max_dirty_pages_pct = 50

innodb_buffer_pool_instances=8

innodb_thread_concurrency=12

##集群复制参数

server-id= 1

[mysqldump]

quick

max_allowed_packet = 64M

[mysql]

no-auto-rehash

# Remove the next comment character if you are not familiar with SQL

#safe-updates

[myisamchk]

key_buffer_size = 20M

sort_buffer_size = 20M

read_buffer = 2M

write_buffer = 2M

[mysqlhotcopy]

interactive-timeout

连接数据库：

# mysql -u root -p (回车后输入密码，没有设置密码再敲一个回车)

查看数据库：

MariaDB [(none)]> show databases ;

创建名为cloudera56的数据库并设置字符集为utf8：

MariaDB [(none)]> create database cloudera56 character set utf8;

授权登陆，并更新：

MariaDB [(none)]> grant all privileges on cloudera56.* to cloudera56@localhost identified by '123456'；

MariaDB [(none)]> grant all privileges on cloudera56.* to cloudera56@'%' identified by '123456';

##第一条授权只能本地登陆，第二条设置任何主机（%代表任何，这里可以填写IP指定主机登陆）都可登陆。（我这里都做了，其实做了第二条就不必再做第一条。）

MariaDB [(none)]> flush privileges;

再查看一下数据库：

MariaDB [(none)]> show databases ;

对我们刚刚创建的数据库cloudera56进行操作：use cloudera56 ；

查看表信息(这里刚刚创建的数据库表为空)：show tables ；

创建数据库表：

CREATE TABLE `USERS` (

  `USER_ID` bigint(20) NOT NULL,

  `USER_NAME` varchar(255) NOT NULL,

  `PASSWORD_HASH` varchar(255) NOT NULL,

  `PASSWORD_SALT` bigint(20) NOT NULL,

  `PASSWORD_LOGIN` tinyint(1) NOT NULL,

  `OPTIMISTIC_LOCK_VERSION` bigint(20) NOT NULL DEFAULT '0',

  PRIMARY KEY (`USER_ID`),

  UNIQUE KEY `unique_user_name` (`USER_NAME`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

查看创建的数据库表：

暂时记录到这里，以后慢慢增加内容 ~~~

本文出自 “小陌成长之路” 博客，请务必保留此出处http://309173854.blog.51cto.com/7370240/1757977