MySQL参数优化及基础操作

kiloyp

系统环境：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;


查看创建的数据库表：


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