分类： Mysql/postgreSQL
2017-04-19 21:16:21
配置主服务器配置
1.下载mysql 5.5源码安装版本，本次版本为mysql 5.5 x86 64位
mysql-5.5.45-linux2.6-x86_64.tar
2.解压缩压缩文件
[ local]# tar -xvf mysql-5.5.45-linux2.6-x86_64.tar.gz
3.建立mysql文件同步链接
[ local]# ln -sv mysql-5.5.45-linux2.6-x86_64 mysql
create symbolic link `mysql' to `mysql-5.5.45-linux2.6-x86_64'
4.改变源码包内文件所有人和所属组
[ mysql]# chown -R root.mysql ./*
[ mysql]# ll
total 220
drwxr-xr-x 2 root mysql 4096 Sep 23 22:50 bin
-rw-r--r-- 1 root mysql 17987 Jun 25 21:44 COPYING
drwxr-xr-x 3 root mysql 4096 Sep 23 22:50 data
drwxr-xr-x 2 root mysql 4096 Sep 23 22:50 docs
drwxr-xr-x 3 root mysql 4096 Sep 23 22:50 include
-rw-r--r-- 1 root mysql 150701 Jun 25 21:44 INSTALL-BINARY
drwxr-xr-x 3 root mysql 4096 Sep 23 22:50 lib
drwxr-xr-x 4 root mysql 4096 Sep 23 22:50 man
drwxr-xr-x 10 root mysql 4096 Sep 23 22:51 mysql-test
-rw-r--r-- 1 root mysql 2496 Jun 25 21:44 README
drwxr-xr-x 2 root mysql 4096 Sep 23 22:50 scripts
drwxr-xr-x 27 root mysql 4096 Sep 23 22:50 share
drwxr-xr-x 4 root mysql 4096 Sep 23 22:50 sql-bench
drwxr-xr-x 2 root mysql 4096 Sep 23 22:50 support-files
[ mysql]# pwd
5.初始化mysql脚本
[ mysql]# scripts/mysql_install_db --user=mysql --datadir=/mydata/data
--必须要做初始化，否则MySQL无法启动
Installing MySQL system tables...
150923 22:58:58 [Note] ./bin/mysqld (mysqld 5.5.45) starting as process 3758 ...
150923 22:58:58 [ERROR] ./bin/mysqld: unknown variable 'usr=mysql'
150923 22:58:58 [ERROR] Aborting
150923 22:58:58 [Note] ./bin/mysqld: Shutdown complete
Installation of system tables failed! Examine the logs in
/mydata/data for more information.
You can try to start the mysqld daemon with:
shell> ./bin/mysqld --skip-grant &
and use the command line tool ./bin/mysql
to connect to the mysql database and look at the grant tables:
shell> ./bin/mysql -u root mysql
mysql> show tables
Try 'mysqld --help' if you have problems with paths. Using --log
gives you a log in /mydata/data that may be helpful.
Please consult the MySQL manual section
'Problems running mysql_install_db', and the manual section that
describes problems on your OS. Another information source are the
MySQL email archives available at
Please check all of the above before submitting a bug report
at
6.复制mysql配置文件
[ mysql]# cp support-files/my-large.cnf /etc/my.cnf
7.复制启动脚本
[ mysql]# cp support-files/mysql.server /etc/init.d/mysqld
8.配置mysql随系统自启动
[ mysql]# chkconfig --add mysqld
9.编辑mysql的配置文件
[ mysql]# vim /etc/my.cnf
.........略
# The following options will be passed to all MySQL clients
[client]
#password = your_password
port = 3306
socket = /tmp/mysql.sock
# Here follows entries for some specific programs
# The MySQL server
[mysqld]
port = 3306
socket = /tmp/mysql.sock
skip-external-locking
key_buffer_size = 256M
max_allowed_packet = 1M
table_open_cache = 256
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size= 16M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8
# Don't listen on a TCP/IP port at all. This can be a security enhancement,
# if all processes that need to connect to mysqld run on the same host.
# All interaction with mysqld must be made via Unix sockets or named pipes.
-------略
--所有和高可用复制相关的功能均在mysqld栏位中进行写配置
#skip-networking
# Replication Master Server (default)
# binary logging is required for replication
log-bin=mysql-bin --mysql 5.5复制的配置文件中已经默认为开启状态
# binary logging format - mixed recommended
binlog_format=mixed
# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id = 1
修改的配置项：
log-bin=master-bin --可根据需要进行重新命名
log-bin-index= master-bin.index --可根据需要进行重新命名，默认的名称就叫log-bin.index
binlog-format=mixed --要么选择为mixed模式，要么选择row模式，一般不建议选择statement模式
binlog_format模式与配置：
① STATEMENT模式（SBR）
每一条会修改数据的sql语句会记录到binlog中。优点是并不需要记录每一条sql语句和每一行的数据变化，减少了binlog日志量，节约IO，提高性能。缺点是在某些情况下会导致master-slave中的数据不一致(如sleep()函数， last_insert_id()，以及user-defined functions(udf)等会出现问题)
② ROW模式（RBR）
不记录每条sql语句的上下文信息，仅需记录哪条数据被修改了，修改成什么样了。而且不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发无法被正确复制的问题。缺点是会产生大量的日志，尤其是alter table的时候会让日志暴涨。
③ MIXED模式（MBR）
以上两种模式的混合使用，一般的复制使用STATEMENT模式保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存binlog，MySQL会根据执行的SQL语句选择日志保存方式。
server-id = 1 --每一个配置的主从复制中，每个server-id都不能一样，默认为1
innodb_file_per_table = 1 --为innodb表空间单独使用单独的数据文件，每个数据库的每个表都会生成一个数据空间
独立表空间：
优点：
1． 每个表都有自已独立的表空间。
2． 每个表的数据和索引都会存在自已的表空间中。
3． 可以实现单表在不同的数据库中移动。
4． 空间可以回收（除drop table操作处，表空不能自已回收）
a) Drop table操作自动回收表空间，如果对于统计分析或是日值表，删除大量数据后可以通过:alter table TableName engine=innodb;回缩不用的空间。
b) 对于使innodb-plugin的Innodb使用turncate table也会使空间收缩。
c) 对于使用独立表空间的表，不管怎么删除，表空间的碎片不会太严重的影响性能，而且还有机会处理。
缺点：
单表增加过大，如超过100个G。
datadir=/mydata/data --配置mysql存放数据文件等文件的路径
--保存退出
10.启动msql，主节点配置完成
[ mysql]# service mysqld start
Starting MySQL... [ OK ]
11.为了配置方便，配置mysql脚本
[ mysql]# vim /etc/profile.d/mysql.sh
export PATH=$PATH:/usr/local/mysql/bin
保存退出
12.执行11步骤配置好的脚本
[ mysql]# . /etc/profile.d/mysql.sh
-------主服务器配置完成
------------------------------------------------------------------------------------------------------------------------------------------------------------
在配置从节点的MySQL服务器之前，需要在主节点配置replication用户，用于主从节点间的同步
1.登录到mysql
[ mysql]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.45-log MySQL Community Server (GPL)
mysql>
2.为MySQL用户同步授权
mysql> grant replication slave on *.* to 'repluser'@'172.16.%.%' identified by 'replpass';
Query OK, 0 rows affected (0.00 sec)
--对于主机的权限，越少越少，最好限定到从服务器的主机
3.刷新MySQL权限
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
------------------------------------------------------------------------------------------------------------------------------------------------------------
配置从服务器配置
1.配置数据文件存放目录
[ ~]# mkdir -pv /mydata/data
mkdir: created directory `/mydata'
mkdir: created directory `/mydata/data'
2.更改MySQL目录的所属人
[ ~]# chown -R mysql.mysql /mydata/data
3.解压缩压缩文件
[ local]# tar -xvf mysql-5.5.45-linux2.6-x86_64.tar.gz
4.建立mysql文件同步链接
[ local]# ln -sv mysql-5.5.45-linux2.6-x86_64 mysql
create symbolic link `mysql' to `mysql-5.5.45-linux2.6-x86_64'
5.改变源码包内文件所有人和所属组
[ mysql]# chown -R root.mysql ./*
[ mysql]# ll
total 220
drwxr-xr-x 2 root mysql 4096 Sep 23 22:50 bin
-rw-r--r-- 1 root mysql 17987 Jun 25 21:44 COPYING
drwxr-xr-x 3 root mysql 4096 Sep 23 22:50 data
drwxr-xr-x 2 root mysql 4096 Sep 23 22:50 docs
drwxr-xr-x 3 root mysql 4096 Sep 23 22:50 include
-rw-r--r-- 1 root mysql 150701 Jun 25 21:44 INSTALL-BINARY
drwxr-xr-x 3 root mysql 4096 Sep 23 22:50 lib
drwxr-xr-x 4 root mysql 4096 Sep 23 22:50 man
drwxr-xr-x 10 root mysql 4096 Sep 23 22:51 mysql-test
-rw-r--r-- 1 root mysql 2496 Jun 25 21:44 README
drwxr-xr-x 2 root mysql 4096 Sep 23 22:50 scripts
drwxr-xr-x 27 root mysql 4096 Sep 23 22:50 share
drwxr-xr-x 4 root mysql 4096 Sep 23 22:50 sql-bench
drwxr-xr-x 2 root mysql 4096 Sep 23 22:50 support-files
[ mysql]# pwd
6.初始化mysql脚本
[ mysql]# scripts/mysql_install_db --user=mysql --datadir=/mydata/data
Installing MySQL system tables...
150923 22:58:58 [Note] ./bin/mysqld (mysqld 5.5.45) starting as process 3758 ...
150923 22:58:58 [ERROR] ./bin/mysqld: unknown variable 'usr=mysql'
150923 22:58:58 [ERROR] Aborting
150923 22:58:58 [Note] ./bin/mysqld: Shutdown complete
Installation of system tables failed! Examine the logs in
/mydata/data for more information.
You can try to start the mysqld daemon with:
shell> ./bin/mysqld --skip-grant &
and use the command line tool ./bin/mysql
to connect to the mysql database and look at the grant tables:
shell> ./bin/mysql -u root mysql
mysql> show tables
Try 'mysqld --help' if you have problems with paths. Using --log
gives you a log in /mydata/data that may be helpful.
Please consult the MySQL manual section
'Problems running mysql_install_db', and the manual section that
describes problems on your OS. Another information source are the
MySQL email archives available at
Please check all of the above before submitting a bug report
at
7.复制mysql配置文件
[ mysql]# cp support-files/my-large.cnf /etc/my.cnf
8.复制启动脚本
[ mysql]# cp support-files/mysql.server /etc/init.d/mysqld
9.配置mysql随系统自启动
[ mysql]# chkconfig --add mysqld
10.配置从服务器配置文件
[ ~]# vim /etc/my.cnf
--添加如下配置
注释掉log-bin=mysql-bin
relay-log = relay-log --配置中继日志
relay-log-index = relay-log.index --配置中继日志索引文件
server-id = 11
11.启动MySQL服务
[ mysql]# service mysqld start
Starting MySQL.. [ OK ]
[ mysql]#
12.为了配置方便，配置mysql脚本
[ mysql]# vim /etc/profile.d/mysql.sh
export PATH=$PATH:/usr/local/mysql/bin
保存退出
13.执行11步骤配置好的脚本
[ mysql]# . /etc/profile.d/mysql.sh
14.连接主服务器开始主从复制
mysql> change master to master_host='192.16.1.11',master_user='repluser',master_password='replpass',master_Log_file='mysql-bin.000001',master_log_pos=339;
Query OK, 0 rows affected (0.03 sec)
mysql> start slave
--参数配置：
master_host --需要连接进行复制的主服务器IP
master_user --用哪个用户进行复制
master_password --用户密码
master_Log_file --从主服务器的第几个二进制日志开始进行复制
--查看主服务器日志信息
mysql> show master status
-> ;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 339 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
mysql> show binlog events in 'mysql-bin.000001'
-> ;
+------------------+-----+-------------+-----------+-------------+------------------------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+-------------+-----------+-------------+------------------------------------------------------------------------------------+
| mysql-bin.000001 | 4 | Format_desc | 1 | 107 | Server ver: 5.5.45-log, Binlog ver: 4 |
| mysql-bin.000001 | 107 | Query | 1 | 264 | grant replication slave on *.* to 'repluser'@'172.16.%.%' identified by 'replpass' |
| mysql-bin.000001 | 264 | Query | 1 | 339 | flush privileges |
+------------------+-----+-------------+-----------+-------------+------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)
master_log_pos --从主服务器第几号日志的什么位置开始进行主从复制
15.查看从节点的复制状态信息
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.16.1.11
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 339
Relay_Log_File: relay-log.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: No
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 339
Relay_Log_Space: 107
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 0
1 row in set (0.00 sec)
关于几个设定信息:
read-only = YES
在从服务器上设定，只能对该从服务器只读访问，禁止写数据，但是对具有supper权限的用户无效；
sync-binlog = ON
在主服务器上设定，用于事物安全，同步主服务器上的binlog至从服务器；
