mysql安装和主从搭建

主库：
CentOS 6.2上MySQL安装：

yum -y install mysql mysql-server mysql-devel mysql-libs

安装完MySQL，更改配置/etc/my.cnf：

[mysqld]
datadir=/data/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
port=3306
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

tmpdir=/tmp
skip_name_resolve

#innodb
innodb_data_file_path=ibdata1:2G;ibdata2:2G;ibdata3:2G;ibdata4:2G;ibdata5:16M:autoextend
innodb_buffer_pool_size = 20G  ##根据服务器内存调整大小(我的内存是32G)
innodb_buffer_pool_instances = 8  
innodb_log_files_in_group = 2
innodb_log_file_size = 1G
innodb_log_buffer_size=200M
innodb_flush_log_at_trx_commit=1
innodb_thread_concurrency=16
innodb_read_io_threads=8
innodb_write_io_threads=16
innodb_open_files=60000
innodb_file_format=Barracuda
innodb_file_per_table=1
innodb_flush_method=O_DIRECT
innodb_stats_on_metadata=0
innodb_strict_mode=1

#myisam
key_buffer_size=64M

#Disable Query Cache
log_error=/var/lib/mysql/mysql-error.log
slow_query_log = on
slow_query_log_file = /data/mysql/mysql-slow.log
long_query_time = 1
log_warnings = 2
query_cache_type = 0
query_cache_size = 0

#binlog
log_bin=/data/mysql/mysql_bin
server_id = 1
binlog_cache_size = 1M
max_binlog_cache_size = 2G
max_binlog_size = 500M
binlog_format = ROW
sync_binlog = 1000
#log_slave_updates = 1
expire_logs_days = 3

#replication
#relay_log=/data/mysql/relaylog
#relay_log_info_file=/data/mysql/relay_log.info
#relay_log_index=/data/mysql/mysqld_relay_bin.index
#skip_slave_start
#replicate-ignore-db=mysql
#replicate-ignore-db=test
#replicate_wild_ignore_table=mysql.%
#replicate_wild_ignore_table=test.%

#server
default_storage_engine = INNODB
character_set_server = utf8
lower_case_table_names = 1
#skip_external_locking
open_files_limit = 65536
local_infile=1
#performance_schema=0

#thread_stack=512K
#thread_cache_size=256
#read_rnd_buffer_size=128K
#sort_buffer_size=256K
#join_buffer_size=128K
#read_buffer_size=128K

#Thread Pool
#thread_handling=pool-of-threads 
max_connections = 10000

max_allowed_packet = 16M
max_connect_errors = 1000000
innodb_io_capacity = 10000

[mysql]
port=3306
prompt=\\u@\\d \\r:\\m:\\s>
default_character_set=utf8
no_auto_rehash

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[client]
port=3306
socket=/var/lib/mysql/mysql.sock

改完配置，启动mysql服务：

service mysqld start

设置开机启动：

chkconfig mysqld on
chkconfig --list mysqld

从库：
CentOS 6.2上MySQL安装，步骤和主库一样，只是配置有些不同，具体如下：

[mysqld]
datadir=/data/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
port=3306
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

tmpdir=/tmp
skip_name_resolve

#innodb
innodb_data_file_path=ibdata1:2G;ibdata2:2G;ibdata3:2G;ibdata4:2G;ibdata5:16M:autoextend
innodb_buffer_pool_size = 20G
innodb_buffer_pool_instances = 8
innodb_log_files_in_group = 2
innodb_log_file_size = 1G
innodb_log_buffer_size=200M
innodb_flush_log_at_trx_commit=1
innodb_thread_concurrency=16
innodb_read_io_threads=8
innodb_write_io_threads=16
innodb_open_files=60000
innodb_file_format=Barracuda
innodb_file_per_table=1
innodb_flush_method=O_DIRECT
innodb_stats_on_metadata=0
innodb_strict_mode=1

#myisam
key_buffer_size=64M

#Disable Query Cache
log_error=/var/lib/mysql/mysql-error.log
slow_query_log = on
slow_query_log_file = /data/mysql/mysql-slow.log
long_query_time = 1
log_warnings = 2
query_cache_type = 0
query_cache_size = 0

#binlog
log_bin=/data/mysql/mysql_bin
server_id=2
binlog_cache_size=1M
max_binlog_cache_size=2G
max_binlog_size=500M
binlog_format=ROW
sync_binlog=1000
#log_slave_updates=1
expire_logs_days=3

#和主库不一样的配置，配置需要同步和忽略哪些库
#replication
relay_log=/data/mysql/relaylog
relay_log_info_file=/data/mysql/relay_log.info
relay_log_index=/data/mysql/mysqld_relay_bin.index
skip_slave_start
replicate-ignore-db=mysql
replicate-ignore-db=test
replicate_wild_ignore_table=mysql.%
replicate_wild_ignore_table=test.%

#server
default_storage_engine=INNODB
character_set_server=utf8
lower_case_table_names=1
#skip_external_locking
open_files_limit=65536
local_infile=1
#performance_schema=0

#thread_stack=512K
#thread_cache_size=256
#read_rnd_buffer_size=128K
#sort_buffer_size=256K
#join_buffer_size=128K
#read_buffer_size=128K

#Thread Pool
#thread_handling=pool-of-threads 
max_connections = 10000

max_allowed_packet = 16M
max_connect_errors = 1000000
innodb_io_capacity = 10000

[mysql]
port=3306
prompt=\\u@\\d \\r:\\m:\\s>
default_character_set=utf8
no_auto_rehash

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[client]
port=3306
socket=/var/lib/mysql/mysql.sock

获取初始密码：

grep "temporary password" /var/lib/mysql/mysql-error.log

刚开始安装完默认无密码，mysql -uroot -p进入数据库后，设置账号和密码：

# 设置root用户密码
set password=password('sdfdsaadsfadffe12323432');
use mysql
grant all privileges  on *.* to 'test'@'10.10.10.%' IDENTIFIED BY 'testtest..112';
flush privileges;
select host,user,password from mysql.user where user = "test";

查看mysql中各个参数是否生效与配置文件中一致：

show variables like '%log_file%';
show variables like 'max_connections';
show status like 'max_used_connections';
show variables like 'key_buffer_size';
show global status like 'key_read%';
show global status like 'thread%';
show variables like 'thread_cache_size';
show global status like 'qcache%';
show variables like 'open_files_limit';
show global status like 'open_files';
show variables like 'slow_query_log';
show variables like 'sql_mode';
show variables like 'max_connections';
show variables like 'innodb_numa_interleave';
show variables like 'innodb_buffer_pool_size';
show variables like 'innodb_buffer_pool_instances';

配置主从
主库上配置从库权限：

use mysql
grant REPLICATION slave on *.* to 'repl'@'10.10.10.12' IDENTIFIED BY 'abcdtestrepl';
flush privileges;
select host,user,password from mysql.user where user = "repl";

从库上配置主库配置：

change master to master_host='10.10.10.11',
master_user='repl',
master_password='abcdtestrepl',
master_log_file='mysql_bin.000001',
master_log_pos=0;

show slave status\G
start slave;
show slave status\G
flush privileges;
show slave status\G

配置完主从，主库上创建数据库，从库上会同步出现数据库，可以建立测试库进行验证一波

其他mysql常用操作
清理binlog日志方式：
先手动清除一波，设置日期之前的binlog都删除，如果binlog还在被slave使用，是不会被删除的

PURGE MASTER LOGS BEFORE '2018-01-20 00:00:00';

在mysql里动态设置binlog过期时间为3天

set global expire_logs_days=3;

在配置文件里修改配置，下次重启时便能自动生效

my.cnf 里面的 expire_logs_days 配置改为3

常用查询sql语句：

#去重统计登录过的uid数目
select count(distinct uid),action from abc_th_action where time between '2018-03-06 00:00:00' and '2018-03-06 23:59:59' and action='login'; 
#去重查询所有登录的uid
select distinct uid from abc_th_action where time between '2018-03-06 00:00:00' and '2018-03-06 23:59:59' and action='login';
#shell命令
mysql -utest -p'aaaaaaa' -e "select distinct uid from abc_online_db.abc_th_action where ACTION='login';" -N -B > login_uid_list.txt
#查看MySql数据库物理文件存放位置
show global variables like "%datadir%";

修改表信息

alter table test rename test1; --修改表名

alter table test add  column name varchar(10); --添加表列

alter table test drop  column name; --删除表列

alter table test modify address char(10) --修改表列类型
||alter table test change address address  char(40)


alter table test change  column address address1 varchar(30)--修改表列名

创建已存在表相同的表结构，不复制数据

CREATE TABLE 新表 SELECT * FROM 旧表 WHERE 1=2