搭建共享mysql数据库_mysql数据库搭建

机器：

[ 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