首先安装依赖库和开发工具
yum -y install cmake bison gcc gcc-c++ autoconf automake make zlib* libxml* ncurses-devel libtool-ltdl-devel* mysql-devel
#如果没有cmake，由于mysql5.5开始，不再使用configure安装，而是使用cmake。所以需要先安装
cmake
wget https://www.cmake.org/files/v2.8/cmake-2.8.6.tar.gz tar -zxvf cmake-2.8.6.tar.gz cd cmake-2.8.6/ ./configure make && make install
wget https://downloads.mysql.com/archives/mysql-5.6/mysql-5.6.12-linux-glibc2.5-x86_64.tar.gz tar xvf mysql-5.6.12.tar.gz cd mysql-5.6.12/ cmake -DCMAKE_INSTALL_PREFIX=/usr/local/webserver/mysql \ -DMYSQL_UNIX_ADDR=/tmp/mysql.sock \ -DDEFAULT_CHARSET=utf8 \ -DDEFAULT_COLLATION=utf8_general_ci \ -DWITH_EXTRA_CHARSETS:STRING=utf8,gbk,gb2312 \ -DWITH_MYISAM_STORAGE_ENGINE=1 \ -DWITH_INNOBASE_STORAGE_ENGINE=1 \ -DWITH_MEMORY_STORAGE_ENGINE=1 \ -DWITH_READLINE=1 \ -DENABLED_LOCAL_INFILE=1 \ -DMYSQL_USER=mysql make make install
ln -s /usr/local/webserver/mysql/lib/libmysqlclient.so.18 /usr/lib/libmysqlclient.so.18
添加mysql用户组和用户
/usr/sbin/groupadd mysql /usr/sbin/useradd -g mysql mysql
设置目录
cd /usr/local/webserver/mysql/support-files/ #在mysql 安装目录下 #如果没有my-large.cnf就拷贝别的如 my-default.cnf，后面需要重新配置my.cnf文件的 cp my-large.cnf /etc/my.cnf #mysql服务文件copy到系统服务目录 cp mysql.server /etc/rc.d/init.d/mysqld chmod +x /etc/init.d/mysqld #修改mysql安装目录相关权限 chown -R mysql:mysql /usr/local/webserver/mysql chmod +w /usr/local/webserver/mysql cd /usr/local/webserver/mysql chown -R root . chown -R mysql data chgrp -R mysql .
设置软连接使mysql, mysqldump, mysqladmin这三个bin命令能在shell中直接运行
sudo ln -s /usr/local/webserver/mysql/bin/mysql /usr/bin sudo ln -s /usr/local/webserver/mysql/bin/mysqldump /usr/bin sudo ln -s /usr/local/webserver/mysql/bin/mysqladmin /usr/bin
启动项相关
chkconfig --add mysqld chkconfig mysqld on
启动服务
service mysqld start
设置密码
/usr/local/webserver/mysql/bin/mysqladmin -u root password "密码"
编辑my.cnf
vim /etc/my.cnf找到
server-id = 1在下面添加一下内容
binlog-do-db=ecstore binlog-ignore-db=mysql binlog-ignore-db=test
ecos02完整的my.cnf内容如下
[client] port = 3306 socket = /tmp/mysql.sock [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 thread_concurrency = 8 log-bin=mysql-bin binlog_format=mixed server-id = 1 binlog-do-db=ecstore binlog-ignore-db=mysql binlog-ignore-db=test [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash [myisamchk] key_buffer_size = 128M sort_buffer_size = 128M read_buffer = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout然后重启mysql
service mysql restart进入mysql命令行
[root@ecos02 ~]# /usr/local/webserver/mysql/bin/mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 9 Server version: 5.6.12-log Source distribution Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>创建数据库ecstore
CREATE DATABASE ecstore;导入信息
GRANT REPLICATION SLAVE ON *.* TO 'ecstore'@'192.168.65.140' IDENTIFIED BY '123456'; # ecstore 是授权的用户名，192.168.65.140 是从服务器的地址，123456 是新授权用户ecstore的密码，这里的用户ecstore不需要自己建立的，会自动创建查看状态
SHOW MASTER STATUS;如果显示如下表示配置成功了
mysql> SHOW MASTER STATUS; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000002 | 1033 | ecstore | mysql,test | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)
编辑my.cnf
vim /etc/my.cnf注释掉server-id=1打开server-id=2
#server-id = 1 server-id = 2
ecos03完整的my.cnf内容如下
[client] port = 3306 socket = /tmp/mysql.sock [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 thread_concurrency = 8 log-bin=mysql-bin binlog_format=mixed server-id = 2 [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash [myisamchk] key_buffer_size = 128M sort_buffer_size = 128M read_buffer = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout
然后重启mysql
service mysqld restart进入mysql命令行
[root@ecos03 ~]# /usr/local/webserver/mysql/bin/mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 9 Server version: 5.6.12-log Source distribution Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>先关闭SLAVE
STOP SLAVE;导入信息
CHANGE MASTER TO MASTER_HOST='192.168.65.142', MASTER_USER='ecstore', MASTER_PASSWORD='123456', MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=1033; # 192.168.65.142 是主服务器的ip地址， # ecstore 是主服务器授权的用户名， # 123456 是用户ecstore的密码， # mysql-bin.000002是在主服务器mysql中通过 # show master status; # 查询到的 字段file的值，这里是对应 mysql-bin.000002, # 1033是对应 Position的值，这里是1033开启SLAVE
START SLAVE;查看同步情况
SHOW SLAVE STATUS\G;如果显示如下表示配置成功
mysql> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.65.142 //主服务器地址
Master_User: ecstore # 授权帐户名，尽量避免使用root
Master_Port: 3306 # 数据库端口，部分版本没有此行
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 1033 # 同步读取二进制日志的位置，大于等于>=Exec_Master_Log_Pos
Relay_Log_File: centos140-relay-bin.000002
Relay_Log_Pos: 982
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes # 此状态必须YES
Slave_SQL_Running: Yes # 此状态必须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: 1033
Relay_Log_Space: 1159
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: d056dc01-fec5-11e2-bada-36a07f9e9822
Master_Info_File: /usr/local/webserver/mysql/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
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
1 row in set (0.00 sec)
ERROR:
No query specified
使用mysqldump备份数据, 设置自动定时备份
备份数据路径
/data/mysqlbackup
创建备份脚本
cat mysql-backup.sh
#!/bin/bash
DBUser=root
DBPasswd="root"
DBHost=192.168.65.142
DBName=`echo "select SCHEMA_NAME from information_schema.SCHEMATA where schema_name not in ('information_schema','test');" | /usr/local/webserver/mysql/bin/mysql -u$DBUser -p$DBPasswd --skip-column-names --silent`
BackupPath=/data/mysqlbackup
LogFile=/data/mysqlbackup/log/mysql_backup.log
NewFile=$(date +%Y%m%d).tgz
OldFile=$(date +%Y%m%d --date='7 days ago').tgz
pushd $BackupPath >> $LogFile
echo $OldFile
echo "-------------------------------------------" >> $LogFile
echo $(date +"%Y-%m-%d %H:%M:%S") >> $LogFile
echo "--------------------------" >> $LogFile
#Delete Old File
if [ -f $OldFile ]
then
rm -f $OldFile >> $LogFile 2>&1
echo "[$OldFile]Delete Old File Success!" >> $LogFile
else
echo "[$OldFile]No Old Backup File!" >> $LogFile
fi
#backup mysql
if [ -f $NewFile ]
then
echo "[$NewFile]The Backup File is exists,Can't Backup!" >> $LogFile
else
for item in $DBName
do
DumpFile=$item".sql"
/usr/local/webserver/mysql/bin/mysqldump -u$DBUser -p$DBPasswd -R --triggers --single-transaction --flush-logs --master-data --database $item > $DumpFile
done
/bin/tar czvf $NewFile *.sql >> $LogFile 2>&1
/bin/rm -f *.sql
fi
echo "-------------------------------------------" >> $LogFile
popd >> $LogFile
自动化
使用crontab定时执行备份脚本
crontab -e添加一条任务
0 4 * * * /bin/bash /data/mysqlbackup/mysql-backup.sh
查看任务
crontab -l
** 解决方法 ** 从服务器上删除掉所有的二进制日志文件，包括一个数据目录下的master.info文件和hostname-relay-bin开头的文件。 master.info:：记录了Mysql主服务器上的日志文件和记录位置、连接的密码。
** 解决方案 ** 由于主服务器运行了一段时间，产生了二进制文件，而slave是从log.000001开始读取的，删除主机二进制文件，包括log.index文件。
** 解决方法 ** 由于slave没有此table表，添加这个表，使用slave start 就可以继续同步。
Error 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' ' at line 1' on query. Default database: 'club'. Query: 'INSERT INTO club.point_process ( GIVEID, GETID, POINT, CREATETIME, DEMO ) VALUES ( 0, 4971112, 5, '2010-12-19 16:29:28', '1 row in set (0.00 sec)
Mysql > SHOW SLAVE STATUS\G; 显示：Slave_SQL_Running 为 NO 解决方法： Mysql > stop slave; Mysql > set global sql_slave_skip_counter =1 ; Mysql > start slave;
Master_Log_File: mysql-bin.000029Read_Master_Log_Pos: 3154083Relay_Log_File: c7-relay-bin.000178Relay_Log_Pos: 633Relay_Master_Log_File: mysql-bin.000025Slave_IO_Running: YesSlave_SQL_Running: NoReplicate_Do_DB: clubReplicate_Ignore_DB:Replicate_Do_Table:Replicate_Ignore_Table:Replicate_Wild_Do_Table:Replicate_Wild_Ignore_Table:Last_Errno: 1594Last_Error: Relay log read failure: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave.Skip_Counter: 0Exec_Master_Log_Pos: 1010663436这个问题原因是，主数据库突然停止或问题终止，更改了mysql-bin.xxx日志，slave服务器找不到这个文件，需要找到同步的点和日志文件，然后chage master即可。
解决方法： change master to master_host='211.103.156.198', master_user='同步帐号', master_password='同步密码', master_port=3306, master_log_file='mysql-bin.000025', master_log_pos=1010663436;
这个错误就说club.question_del 表里面没有qdir这个字段 造成的加上就可以了~！ 在主的mysql ： 里面查询 Desc club.question_del； 在 错误的从服务器上执行 ： alter table question_del add qdir varchar(30) not null;
这个错误就是IO 进程没连接上 ，想办法连接上把 把与主的POS 号和文件一定要对，然后重新加载下数据。具体步骤： slave stop; change master to master_host='IP地址',master_user='club',master_password='mima ',master_log_file='mysqld-bin.000048',MASTER_LOG_POS=396549485; 注：master_log_file='mysqld-bin.000048',MASTER_LOG_POS=396549485;是从主的上面查出 来的 ：show master status\G; LOAD DATA FROM MASTER; load data from master； slave start;