centos6.5单机编译配置mysql主从

2016-07-12 10:22:00来源:oschina作者:蛋定龙人点击

系统:centos6.5


mysql版本:5.6.26


参考上一篇:http://my.oschina.net/dandinglong/blog/507902


下载mysql源码到/mydata目录,解压


tarzxvfmysql-5.6.26.tar.gz
cdmysql-5.6.26

使用cmake进行配置


因为需要在单机上装2个实例,所有配置时需要制定安装目录等信息


[root@localhostmysql-5.6.26]#cmake/
>-DCMAKE_INSTALL_PREFIX=/usr/local/mysql3306/
>-DSYSCONFDIR=/usr/local/mysql3306/
>-DWITH_INNOBASE_STORAGE_ENGINE=1/
>-DMYSQL_UNIX_ADDR=/usr/local/mysql3306/mysql.sock/
>-DMYSQL_TCP_PORT=3306/
>-DDEFAULT_CHARSET=utf8/
>-DDEFAULT_COLLATION=utf8_general_ci/
>-DEXTRA_CHARSETS=all

反斜杠加回车,cmake不会执行,而是等待继续输入。如上代码


配置解释:


-DCMAKE_INSTALL_PREFIX 实例安装的目录


-DSYSCONFDIR 配置文件my.cnf错在路径


-DWITH_INNOBASE_STORAGE_ENGINE安装innodb引擎


-DMYSQL_UNIX_ADDR 实例使用的socket文件


-DMYSQL_TCP_PORT 实例监听的端口号


-DDEFAULT_CHARSET 默认字符编码


-DDEFAULT_COLLATION 默认字符排序方式


-DEXTRA_CHARSETS 额外数据集mysql官网5.6版本没找到该配置。暂时先设着吧。

然后就是make 和 make install


make
makeinstall

进入安装目录


cd/usr/local/mysql3306

配置用户用户组


[root@localhostmysql3306]#chown-Rmysql.
[root@localhostmysql3306]#chgrp-Rmysql.

转移/ect/my.cnf


[root@localhostmysql3306]#mv/etc/my.cnf/etc/my.cnf.bak

初始化数据库


[root@localhostmysql3306]#scripts/mysql_install_db--user=mysql

还权限给root,data是mysql的


chown-Rroot.
chown-Rmysqldata

启动mysql


[root@localhostmysql3306]#support-files/mysql.serverstart
StartingMySQL.SUCCESS!

变更root密码


[root@localhostmysql3306]#bin/mysqladmin-urootpassword"root"

登录mysql


[root@localhostmysql3306]#bin/mysql-uroot-p

查看表


mysql>showdatabases;
+--------------------+
|Database|
+--------------------+
|information_schema|
|mysql|
|performance_schema|
|test|
+--------------------+
4rowsinset(0.00sec)

第一实例安装好了


安装第二个实例


进入/mydata/mysql-5.6.26目录


查看目录文件,发现有一个CMakeCache.txt文件,这个是cmake的缓存文件,编译第二个实例需要删除他


rm-fCMakeCache.txt

执行cmake


[root@localhostmysql-5.6.26]#cmake/
>-DCMAKE_INSTALL_PREFIX=/usr/local/mysql3307/
>-DSYSCONFDIR=/usr/local/mysql3307/
>-DWITH_INNOBASE_STORAGE_ENGINE=1/
>-DMYSQL_UNIX_ADDR=/usr/local/mysql3307/mysql.sock/
>-DMYSQL_TCP_PORT=3307/
>-DDEFAULT_CHARSET=utf8/
>-DDEFAULT_COLLATION=utf8_general_ci/
>-DEXTRA_CHARSETS=all
make
makeinstall

进入/usr/local/mysql3307 目录


配置完用户组


[root@localhostmysql3306]#chown-Rmysql.
[root@localhostmysql3306]#chgrp-Rmysql.

执行数据库初始化


[root@localhostmysql3306]#scripts/mysql_install_db--user=mysql

启动mysql


[root@localhostmysql3307]#support-files/mysql.serverstart

发现出现如下报错


StartingMySQL....ERROR!TheserverquitwithoutupdatingPIDfile(/usr/local/mysql3307/data/localhost.localdomain.pid).

这个问题很头大,查了资料没查到合适的,最后看了错误日志data/localhost.localdomain.err


找到这么一段,


Can't start server : Bind on unix socket: Permission denied


Do you already have another mysqld server running on socket: /usr/local/mysql3307/mysql.sock ?感觉应该是mysql.sock的权限的问题,在cmake时候这样配置的sock


-DMYSQL_UNIX_ADDR=/usr/local/mysql3307/mysql.sock


查看了/usr/local/mysql3307的用户为root,然而mysql启动的权限是mysql,所以在3307目录下创建不了mysql.sock


chown-Rmysql/usr/local/mysql3307

然后


support-files/mysql.serverstart

Starting MySQL. SUCCESS!


mysql 启动了


之后改个root密码


bin/mysqladmin-urootpassword"root"

此时3306和3307都启动了,可以开始配置主从了。

我这里以3306为主,3307为从


编辑3306的my.cnf


cmake 时候做了如下配置


-DSYSCONFDIR=/usr/local/mysql3306


所以my.cnf就在安装目录下,同样3307的my.cnf也在安装目录mysql3307下


[mysqld]
log-bin=mysql-bin
server-id=1

开启二进制日志,并设置服务ID为1,服务ID随便设,不要跟从重复就行


重启mysql3306


[root@localhostmysql3306]#support-files/mysql.serverrestart
ShuttingdownMySQL..SUCCESS!
StartingMySQL..SUCCESS!

进入mysql命令行


[root@localhostmysql3306]#bin/mysql-uroot-p

查看主的状态


mysql>showmasterstatus/G
***************************1.row***************************
File:mysql-bin.000001
Position:120
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1rowinset(0.00sec)

记录下File和Position,在配置从的时候要用到


然后为主添加一个账号,给从连接主用的


mysql>grantreplicationslave,replicationclienton*.*torepl@'localhost'identifiedby'repl';
mysql>flushprivileges;

主(3306)配置完成,开始配置从(3307)


编辑my.cnf文件


[mysqld]
server-id=2

保存并重启从


[root@localhostmysql3307]#support-files/mysql.serverrestart
ShuttingdownMySQL.SUCCESS!
StartingMySQL..SUCCESS!

进入mysql命令行输入如下配置


mysql>changemastertomaster_host='localhost',MASTER_PORT=3306,master_user='repl',master_password='repl',master_log_file='mysql-bin.000001',master_log_pos=120;

启动从(slave)


mysql>startslave;

查看从状态


mysql>showslavestatus/G
***************************1.row***************************
Slave_IO_State:Waitingformastertosendevent
Master_Host:localhost
Master_User:repl
Master_Port:3306
Connect_Retry:60
Master_Log_File:mysql-bin.000001
Read_Master_Log_Pos:1396
Relay_Log_File:localhost-relay-bin.000002
Relay_Log_Pos:283
Relay_Master_Log_File:mysql-bin.000001
Slave_IO_Running:Yes
Slave_SQL_Running:Yes
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:1396
Relay_Log_Space:460
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:0
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:1
Master_UUID:1570ef3f-5e96-11e5-b64e-000c2970d0cb
Master_Info_File:/usr/local/mysql3307/data/master.info
SQL_Delay:0
SQL_Remaining_Delay:NULL
Slave_SQL_Running_State:Slavehasreadallrelaylog;waitingfortheslaveI/Othreadtoupdateit
Master_Retry_Count:86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position:0
1rowinset(0.00sec)

启动成功,尝试同步,这里用test表测试


从运营下列命令,看到test库现在是空的。


mysql>usetest;
Databasechanged
mysql>showtables;
Emptyset(0.00sec)

同样主的test库也是空的


mysql>usetest;
Databasechanged
mysql>showtables;
Emptyset(0.00sec)

我们在主库的test库创建一个mytest表,然后看从库是否同步


mysql>usetest;
Databasechanged
mysql>createtablemytest(
->idint(11),
->namevarchar(20)
->);
QueryOK,0rowsaffected(0.02sec)
mysql>showtables;
+----------------+
|Tables_in_test|
+----------------+
|mytest|
+----------------+
1rowinset(0.00sec)

上面show tables 的结果看到创建了mytest表


在从库输入showt ables;


mysql>showtables;
+----------------+
|Tables_in_test|
+----------------+
|mytest|
+----------------+
1rowinset(0.00sec)

同步成功。


设置主从的时候,我配到点问题,主要是应为主库的账号没有设置好,如果碰到同样的问题,可以直接复制我上面的账号,或者用其他账号多试试。

到这里主从配置成功。

最新文章

123

最新摄影

微信扫一扫

第七城市微信公众平台