机器：
[ 192.168.12.90 3306/3307] bje-qa-mysql-01 /100G /home 178G /data 6.3T
[ 192.168.12.91 3306/3307] bje-qa-mysql-02 /100G /home 178G /data 6.3T
[ 192.168.12.92 3306] bje-qa-mysql-03 /100G /home 178G /data 6.3T
[ 192.168.12.93 3306] bje-qa-mysql-04 /100G /home 178G /data 6.3T
[ 192.168.12.94 3306] bje-qa-mysql-05 /100G /home 178G /data 6.3T
[ 192.168.12.95 3306] bje-qa-mysql-06 /100G /home 178G /data 6.3T
192.168.65.15：6015端口
---------------------------------
一、搭建单个实例
1.安装依赖包(#已安装#)
yum install cmake make gcc gcc-c++ biso ncurses ncurses-devel
2.手动创建创建数据目录
cd /data/
mkdir mysql3307
cd mysql3307/
mkdir data etc log binlog innodata innolog relaylog
ll
cd log/
touch mysqld.err
3.新建mysql用户组和用户，并改变新建数据目录的属组和属主：
groupadd mysql (#已创建#）
useradd -r -g mysql mysql (#已创建#）
cd /data/
chown -R mysql:mysql /data/mysql3307
4、下载、解压安装包
mkdir -p /data/soft
cd /data/soft
wget https://cdn.mysql.com//Downloads/MySQL-5.5/mysql-5.5.58.tar.gz（#已下载#）
解压：
tar -zxf mysql-5.5.58-linux-glibc2.12-x86_64.tar.gz（#已解压#）
5、在解压目录中进行cmake编译：
cd /data/soft/mysql-5.5.58/
cmake .
-DCMAKE_INSTALL_PREFIX=/data/mysql3307/
-DMYSQL_DATADIR=/data/mysql3307/data
-DSYSCONFDIR=/data/mysql3307/etc
-DWITH_INNOBASE_STORAGE_ENGINE=1
-DWITH_READLINE=1
-DMYSQL_UNIX_ADDR=/data/mysql3307/mysql.sock
-DMYSQL_TCP_PORT=3307
-DENABLED_LOCAL_INFILE=1
-DWITH_PARTITION_STORAGE_ENGINE=1
-DEXTRA_CHARSETS=all
-DDEFAULT_CHARSET=utf8
-DDEFAULT_COLLATION=utf8_general_ci
6.make&&make install
make -j8
make install
5.配置文件的修改
vi /data/mysql3307/etc/my3307.cnf
#注意：使用线上配置文件，则cnf文件中出现的目录和文件，除了mysqld.pid和mysql.sock（这两个文件在服务启动过程中会自动生成）文件之外，都要事先创建好。
6.初始化系统表（在安装目录中进行）
cd /data/mysql3307/
scripts/mysql_install_db --user=mysql --datadir=/data/mysql3307/data/
#出现两个OK即为成功
7.使用配置文件启动服务
cd /data/mysql3307/bin
./mysqld_safe --defaults-file=/data/mysql3307/etc/my3307.cnf &
ps -ef|grep mysql
#查看服务起来则安装成功
8、安装完成MySQL后第一时间删除(!=)root或者host不是localhost的用户：
./mysql 登录
select user,host,password from mysql.user;
delete from mysql.user where user not in('root','localhost');
再次检查一下用户：
select user,host,password from mysql.user;
9、为mysql数据库创建root密码和新用户名密码
给root用户设置密码：
update mysql.user set password=password('root123') where user='root';
flush privileges;
验证：
exit
mysql -uroot -proot123 --socket=/data/mysql3307/mysql.sock
创建新用户：
grant all privileges on *.* to 'tester'@'%' identified by 'nopass.2';
flush privileges;
select user,host,password from mysql.user;
验证：
exit
mysql -utester -pnopass.2 --socket=/data/mysql3307/mysql.sock
##命令
ps -ef|grep mysqld查看mysql进程
二、搭建主从
第一步：从库也先搭建单个实例，注意配置文件中的server_id要设置不同
（同时）
主库上创建主从同步账号：
mysql -uroot -proot123 --socket=/data/mysql3307/mysql.sock
grant replication slave on *.* to 'repl'@'%' identified by 'repl123';
flush privileges;
验证：
mysql -urepl -prepl123 --socket=/data/mysql3307/mysql.sock
查看主库当前的二进制日志pos：
mysql -uroot -proot123 --socket=/data/mysql3307/mysql.sock
mysql> show master status;
+----------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+----------+----------+--------------+------------------+
| 0.000001 | 1122 | | |
+----------+----------+--------------+------------------+
1 row in set (0.00 sec)
从库上：
CHANGE MASTER TO
MASTER_HOST='192.168.12.90',
MASTER_USER='repl',
MASTER_PASSWORD='repl123',
MASTER_PORT=3307,
MASTER_LOG_FILE='0.000001',
MASTER_LOG_POS=1122,
MASTER_CONNECT_RETRY=60;
start slave；
show slave status G;
##在这里主要是看:
Slave_IO_Running=Yes
Slave_SQL_Running=Yes
Second_Behind_Master=0
三、导出导入数据（以192.168.65.15：6015为例）
1、查看库:
mysql -u'tester' -S /data/mysql6015/mysql.sock -pnopass.2 -P 6015
2、导出数据：
[备份前查看一下数据大小]
cd /data/mysql6015/data
du -sh
查看不了可以用sudo su查看
*备份所有数据库：（可选）
time mysqldump -utester -pnopass.2 -A--single-transaction --socket=/data/mysql6015/mysql.sock -P6015 > /tmp/Backup01.sql
备份指定数据库：
time mysqldump -utester -pnopass.2 --databases hotel_product_single hotel_confirm hotel_cashout hotel_schedule --single-transaction --socket=/data/mysql6015/mysql.sock -P6015 > /tmp/Backup01.sql
例如:
hotel_product_single
hotel_confirm
hotel_cashout
hotel_schedule
3、拷贝备份文件：
scp /tmp/Backup01.sql tester@192.168.12.90:/tmp/
4、导入数据：
目标实例上：
ll /tmp
mysql -utester -pnopass.2 --socket=/data/mysql3306/mysql.sock
source /tmp/Backup01.sql
##主库配置文件 [mysqld] datadir=/data/mysql3307/data socket=/data/mysql3307/mysql.sock pid-file=/data/mysql3307/mysqld.pid general_log=1 general_log_file=/data/mysql3307/log/mysql.log log-error=/data/mysql3307/log/mysqld.err log-bin=/data/mysql3307/binlog/mysql-bin.log log-bin-index=/data/mysql3307/binlog/mysql-bin.index log_bin_trust_function_creators=1 log_bin=0 read_only=0 ##主库为0，关闭## server_id=330790 expire_logs_days=7 binlog_format=mixed max_binlog_size=1024MB user=mysql default-storage-engine=innodb port=3307 character_set_server=utf8 skip_name_resolve wait_timeout=3600 #################################### #InnoDB #################################### innodb_data_home_dir=/data/mysql3307/innodata # [InnoDB] innodb_file_per_table=1 # [InnoDB]独立表空间开关 innodb_data_file_path=ibdata1:64M:autoextend # [InnoDB]共享表空间 innodb_flush_log_at_trx_commit=1 # [InnoDB] innodb_log_group_home_dir=/data/mysql3307/innolog # [InnoDB Log] innodb_log_file_size=512M # [InnoDB Log] innodb_log_files_in_group=3 # [InnoDB Log] innodb_lock_wait_timeout=100 #innodb_flush_method=O_DIRECT #Direct IO #innodb_sync_spin_loops=0 #innodb_io_capacity=2000 innodb_file_io_threads=4 innodb_max_dirty_pages_pct=80 innodb_thread_concurrency=16 #################################### #Replication #################################### relay-log=/data/mysql3307/relaylog/mysql-relay-bin.log relay-log-index=/data/mysql3307/relaylog/mysql-relay-bin.index ##must be multi-lines,don't seperated by comma #replicate-do-db = #replicate-do-db = #replicate-ignore-db = mysql #replicate-ignore-db = test #slave-skip-errors = all # use [mk-slave-restart] #log-slave-updates=1 #report-host=10.91.64.3 #report-port=6231 slave-net-timeout = 300 relay_log_purge=0 #################################### #Slow Query #################################### slow-query-log=1 slow-query-log-file=/data/mysql3307/log/slow.log long-query-time=0.5 #################################### #Global Memory #################################### max_connections = 2020 max_user_connections=1900 max_connect_errors=10000 thread_concurrency = 8 max_allowed_packet = 48M max_binlog_cache_size=256M query_cache_limit=2M max_tmp_tables=256 interactive_timeout=300 binlog_cache_size = 2M table_cache = 1024 thread_cache_size = 1200 query_cache_size = 32M key_buffer = 16M # [MyISAM] innodb_log_buffer_size = 8M # [InnoDB] innodb_buffer_pool_size= 2048M # [InnoDB,一般设置成机器内存的50%~80%，测试机1GB] #################################### #Thread Private #################################### sort_buffer_size = 2M thread_stack = 256K join_buffer_size = 4M read_buffer_size = 4M read_rnd_buffer_size = 4M net_buffer_length = 16384 bulk_insert_buffer_size = 4M tmp_table_size = 256M max_heap_table_size = 16M [mysql] prompt=u@h:p> pager=less -SFX [client] socket = /data/mysql3307/mysql.sock
##从库配置文件 [mysqld] datadir=/data/mysql3307/data socket=/data/mysql3307/mysql.sock pid-file=/data/mysql3307/mysqld.pid general_log=1 general_log_file=/data/mysql3307/log/mysql.log log-error=/data/mysql3307/log/mysqld.err log-bin=/data/mysql3307/binlog/mysql-bin.log log-bin-index=/data/mysql3307/binlog/mysql-bin.index log_bin_trust_function_creators=1 log_bin=0 read_only=1 ##从库为1，开启## server_id=330791 expire_logs_days=7 binlog_format=mixed max_binlog_size=1024MB user=mysql default-storage-engine=innodb port=3307 character_set_server=utf8 skip_name_resolve wait_timeout=3600 #################################### #InnoDB #################################### innodb_data_home_dir=/data/mysql3307/innodata # [InnoDB] innodb_file_per_table=1 # [InnoDB]独立表空间开关 innodb_data_file_path=ibdata1:64M:autoextend # [InnoDB]共享表空间 innodb_flush_log_at_trx_commit=1 # [InnoDB] innodb_log_group_home_dir=/data/mysql3307/innolog # [InnoDB Log] innodb_log_file_size=512M # [InnoDB Log] innodb_log_files_in_group=3 # [InnoDB Log] innodb_lock_wait_timeout=100 #innodb_flush_method=O_DIRECT #Direct IO #innodb_sync_spin_loops=0 #innodb_io_capacity=2000 innodb_file_io_threads=4 innodb_max_dirty_pages_pct=80 innodb_thread_concurrency=16 #################################### #Replication #################################### relay-log=/data/mysql3307/relaylog/mysql-relay-bin.log relay-log-index=/data/mysql3307/relaylog/mysql-relay-bin.index ##must be multi-lines,don't seperated by comma #replicate-do-db = #replicate-do-db = #replicate-ignore-db = mysql #replicate-ignore-db = test #slave-skip-errors = all # use [mk-slave-restart] #log-slave-updates=1 #report-host=10.91.64.3 #report-port=6231 slave-net-timeout = 300 relay_log_purge=0 #################################### #Slow Query #################################### slow-query-log=1 slow-query-log-file=/data/mysql3307/log/slow.log long-query-time=0.5 #################################### #Global Memory #################################### max_connections = 2020 max_user_connections=1900 max_connect_errors=10000 thread_concurrency = 8 max_allowed_packet = 48M max_binlog_cache_size=256M query_cache_limit=2M max_tmp_tables=256 interactive_timeout=300 binlog_cache_size = 2M table_cache = 1024 thread_cache_size = 1200 query_cache_size = 32M key_buffer = 16M # [MyISAM] innodb_log_buffer_size = 8M # [InnoDB] innodb_buffer_pool_size= 2048M # [InnoDB,一般设置成机器内存的50%~80%，测试机1GB] #################################### #Thread Private #################################### sort_buffer_size = 2M thread_stack = 256K join_buffer_size = 4M read_buffer_size = 4M read_rnd_buffer_size = 4M net_buffer_length = 16384 bulk_insert_buffer_size = 4M tmp_table_size = 256M max_heap_table_size = 16M [mysql] prompt=u@h:p> pager=less -SFX [client] socket = /data/mysql3307/mysql.sock