本文共 11072 字,大约阅读时间需要 36 分钟。
一、 概要 # 版本:centos64, MySQL-5.6.27 # 操作系统安装用户:appuser # 数据库连接用户:root # 192.168.56.250:主备,(id:25006,库名:test,port:3306)、(id:25007,库名:test,port:3307); # 192.168.56.251:备主,(id:25106,库名:test,port:3306)、(id:25107,库名:test,port:3307) # JDBC连接设置,192.168.56.250:3306、192.168.56.251:3307 # 添加新库时,需要更改my.cnf中的binlog-do-db、replicate-do-db,并重启数据库,以实现新库的主从同步 #本地连接 su - appuser mysql -S /appl/mysql/data/dbdata_3306/mysql.sock -uroot -p #远程连接 mysql -h 192.168.56.250 -P3306 -uroot –p #配置路径 /appl/mysql/my.cnf #重启命令 停:ps -ef|grep mysql, kill <相应线程> 启:su - appuser;mysqld_multi --defaults-file=/appl/mysql/my.cnf start 1-2 测:mysqld_multi --defaults-file=/appl/mysql/my.cnf report 1-3;登录备库,show slave status\G 二、 准备工作 # 下载 http://mirrors.sohu.com/mysql/MySQL-5.6/ # 如有旧版,先uninstall旧版本 ps -ef|grep mysql kill <pid> rpm -qa|grep -i mysql(-i 忽略大小写) rpm -e mysql-libs-5.1.61-4.el6.x86_64 --nodeps rpm -e mysql-xxx --nodeps # 如没有安装用户,先创建安装用户(适合生产环境) groupadd appuser useradd -d /home/appuser -m appuser -g appuser -p appuser passwd appuser id appuser # 改为安装用户权限 chown -R appuser:appuser /mk/soft/mysql-5.6.26-linux-glibc2.5-x86_64.tar.gz # 解压重命名 su - appuser cd /mk/soft tar -xvzf mysql-5.6.26-linux-glibc2.5-x86_64.tar.gz -C /appl (生产上一般为:/usr/local) cd /appl mv mysql-5.6.26-linux-glibc2.5-x86_64 mysql cd /appl/mysql cp support-files/my-default.cnf /appl/mysql/my.cnf # copy配置文件 ctrl+D返回root cd /appl/mysql cp support-files/mysqld_multi.server /etc/init.d/mysqld # cp support-files/my-default.cnf /etc/my.cnf #不使用此法 ln -s bin/my_print_defaults /usr/bin/ # 调用路径配置 vi /etc/profile MYSQL_HOME=/appl/mysql export PATH=$PATH:$MYSQL_HOME/bin source /etc/profile # 开启remote端口给主机访问 /sbin/iptables -I INPUT -p tcp --dport 3306 -j ACCEPT /sbin/iptables -I INPUT -p tcp --dport 3307 -j ACCEPT /etc/init.d/iptables save service iptables restart 三、 数据库配置 # 数据库配置 su - appuser vi /appl/mysql/my.cnf 1、 服务器1 ##### 服务器:Server 1 ##### [mysqld_multi] mysqld = /appl/mysql/bin/mysqld_safe mysqladmin = /appl/mysql/bin/mysqladmin user = root password = root [mysqld1] server-id = 25006 log_bin=mysql-bin binlog-do-db=test binlog-ignore-db=mysql port = 3306 socket = /appl/mysql/data/dbdata_3306/mysql.sock pid-file = /appl/mysql/data/dbdata_3306/3306.pid datadir = /appl/mysql/data/dbdata_3306 log-bin=/appl/mysql/log/log_bin_3306 character_set_server=utf8 lower_case_table_names=1 # skip-grant-tables [mysqld2] server-id = 25007 replicate-ignore-db = mysql replicate-do-db = test port = 3307 socket = /appl/mysql/data/dbdata_3307/mysql.sock pid-file = /appl/mysql/data/dbdata_3307/3307.pid datadir = /appl/mysql/data/dbdata_3307 log_slave_updates = 1 character_set_server=utf8 lower_case_table_names=1 2、 服务器2 ##### 服务器:Server 2 ##### [mysqld_multi] mysqld = /appl/mysql/bin/mysqld_safe mysqladmin = /appl/mysql/bin/mysqladmin user = root password = root [mysqld1] server-id = 25106 replicate-ignore-db = mysql replicate-do-db = test port = 3306 socket = /appl/mysql/data/dbdata_3306/mysql.sock pid-file = /appl/mysql/data/dbdata_3306/3306.pid datadir = /appl/mysql/data/dbdata_3306 # log-bin = /appl/mysql/appl/mysql/log/log_bin_3306 # relay_log = /appl/mysql/appl/mysql/log/relay_log log_slave_updates = 1 character_set_server=utf8 lower_case_table_names=1 # skip-grant-tables [mysqld2] server-id = 25107 log_bin=mysql-bin binlog-do-db=test binlog-ignore-db=mysql port = 3307 socket = /appl/mysql/data/dbdata_3307/mysql.sock pid-file = /appl/mysql/data/dbdata_3307/3307.pid datadir = /appl/mysql/data/dbdata_3307 log-bin=/appl/mysql/log/log_bin_3307 character_set_server=utf8 lower_case_table_names=1 ##### 四、 安装 # 安装实例 su - appuser cd /appl/mysql/data mkdir dbdata_3306 cd /appl/mysql ./scripts/mysql_install_db --defaults-file=/appl/mysql/my.cnf --basedir=/appl/mysql --datadir=/appl/mysql/data/dbdata_3306 --user=appuser mkdir dbdata_3307 cd /appl/mysql ./scripts/mysql_install_db --defaults-file=/appl/mysql/my.cnf --basedir=/appl/mysql --datadir=/appl/mysql/data/dbdata_3307 --user=appuser 五、 启动 # 启动实例 su - appuser cd /appl/mysql mkdir log # mysqld_multi --defaults-file=/appl/mysql/my.cnf stop 1 -password=appuser (或:ps -ef|grep mysql, kill相应线程) mysqld_multi --defaults-file=/appl/mysql/my.cnf start 1 mysqld_multi --defaults-file=/appl/mysql/my.cnf report 1 mysqld_multi --defaults-file=/appl/mysql/my.cnf start 2 mysqld_multi --defaults-file=/appl/mysql/my.cnf report 2 # log /appl/mysql/data/dbdata_3306/<hostname>.err /appl/mysql/data/dbdata_3307/<hostname>.err # 验证 lsof -i:3306 (或netstat -anp|grep 3306) lsof -i:3307 六、 权限配置 # 设数据库权限 su - appuser cd /appl/mysql mysqladmin -uroot password 'root' -S ./data/dbdata_3306/mysql.sock mysqladmin -uroot password 'root' -S ./data/dbdata_3307/mysql.sock mysql -uroot -S ./data/dbdata_3306/mysql.sock -p mysql -uroot -S ./data/dbdata_3307/mysql.sock -p show databases; use mysql; select host,user,password from user; UPDATE user SET Password=PASSWORD('root') where USER='root'; INSERT INTO `user` ( `Host` , `User` , `Password` , `Select_priv` , `Insert_priv` , `Update_priv` , `Delete_priv` , `Create_priv` , `Drop_priv` , `Reload_priv` , `Shutdown_priv` , `Process_priv` , `File_priv` , `Grant_priv` , `References_priv` , `Index_priv` , `Alter_priv` , `Show_db_priv` , `Super_priv` , `Create_tmp_table_priv` , `Lock_tables_priv` , `Execute_priv` , `Repl_slave_priv` , `Repl_client_priv` , `ssl_type` , `ssl_cipher` , `x509_issuer` , `x509_subject` , `max_questions` , `max_updates` , `max_connections` ) VALUES ('%', 'root', PASSWORD( 'root' ) , 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', '', '', '', '', '0', '0', '0'); # INSERT INTO `user` ( `Host` , `User` , `Password` , `Select_priv` , `Insert_priv` , `Update_priv` , `Delete_priv` , `Create_priv` , `Drop_priv` , `Reload_priv` , `Shutdown_priv` , `Process_priv` , `File_priv` , `Grant_priv` , `References_priv` , `Index_priv` , `Alter_priv` , `Show_db_priv` , `Super_priv` , `Create_tmp_table_priv` , `Lock_tables_priv` , `Execute_priv` , `Repl_slave_priv` , `Repl_client_priv` , `ssl_type` , `ssl_cipher` , `x509_issuer` , `x509_subject` , `max_questions` , `max_updates` , `max_connections` ) VALUES ('%', 'guest', PASSWORD( 'guest' ) , 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', '', '', '', '', '0', '0', '0'); # INSERT INTO `user` ( `Host` , `User` , `Password` , `Select_priv` , `Insert_priv` , `Update_priv` , `Delete_priv` , `Create_priv` , `Drop_priv` , `Reload_priv` , `Shutdown_priv` , `Process_priv` , `File_priv` , `Grant_priv` , `References_priv` , `Index_priv` , `Alter_priv` , `Show_db_priv` , `Super_priv` , `Create_tmp_table_priv` , `Lock_tables_priv` , `Execute_priv` , `Repl_slave_priv` , `Repl_client_priv` , `ssl_type` , `ssl_cipher` , `x509_issuer` , `x509_subject` , `max_questions` , `max_updates` , `max_connections` ) VALUES ('localhost', 'guest', PASSWORD( 'guest' ) , 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', '', '', '', '', '0', '0', '0'); GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY 'root' WITH GRANT OPTION; GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'root' WITH GRANT OPTION; FLUSH PRIVILEGES; # REVOKE ALL ON *.* FROM 'test'@'127.0.0.1'; # REVOKE ALL ON *.* FROM 'test'@'localhost'; # GRANT ALL PRIVILEGES ON test.* TO 'test'@'127.0.0.1' IDENTIFIED BY 'test'; # GRANT ALL PRIVILEGES ON test.* TO 'test'@'localhost' IDENTIFIED BY 'test'; # FLUSH PRIVILEGES; select host,user,password from user; 七、 主从配置 # 主库设置 1: grant replication slave on *.* to 'appuser'@'192.168.56.251' identified by 'appuser' with grant option; 25107: grant replication slave on *.* to 'appuser'@'192.168.56.250' identified by 'appuser' with grant option; FLUSH PRIVILEGES; show master status; # 注意:这里记住File的值和Position的值,后面会用到 exit; # 备库设置 stop slave; #执行同步,这里用上刚才的File的值和Position的值 25106: change master to master_host='192.168.56.250', master_port = 3306, master_user='appuser', master_password='appuser', master_log_file='log_bin_3306.000001' ,master_log_pos=262; 2: change master to master_host='192.168.56.251', master_port = 3307, master_user='appuser', master_password='appuser', master_log_file='log_bin_3307.000001' ,master_log_pos=1086; start slave; SHOW SLAVE STATUS\G # 注意:查看Slave_IO_Running: Yes;Slave_SQL_Running: Yes # 查看log: /opt/mysql/data/dbdata_330n/<hostname>.err # 验证主备同步 # 主库 use test; drop table test01; CREATE TABLE test01 ( id int not null primary key,name char(20) ); truncate test01; insert into test01 values(1,'a'); commit; select * from test01; # 备库 use test; show tables; 八、 其他 # 开机自启动 chkconfig --add mysqld chkconfig --level 345 mysqld on chkconfig --list mysqld # 导出导入 #导出 flush tables with read lock; mysqldump -u root -p osyunweidb > /home/osyunweidbbak.sql (导出数据库osyunweidb) unlock tables; #导入 (1) cd /opt/tomcat/mysqldump mysql -h xxx -P -uroot -pxxx -S /opt/mysql/data/dbdata_330n/mysql.sock 库名<xxx.sql # e.g. mysql -h 192.168.56.250 -P3306 -uroot -proot -S /opt/mysql/data/dbdata_3306/mysql.sock db_ind<db_ind.sql (2) mysql -u root -P3306 create database osyunweidb; use osyunweidb source /home/osyunweidbbak.sql (或mysql -u root -S /tmp/mysqlB.sock xxx < /root/xxx1.sql) # Q&A Q: Slave_IO_Running: No A: (1)test with "sftp appuser@192.168.56.250"; (2)/appl/mysql/data/dbdata_3306/<hostname>.err Q: Slave_SQL_Running: No A: (1)重启master & start slave; (2)初始时主备库要一样 Q: error connecting to master '' retries:1 error_code: 1045 Q: host name lookup failure A: (1)检测远程端口是否打开nc -v 192.168.56.251 3306; (2)vi /etc/hosts, 加入ip hostname, 同时hostname 加入到127.0.0.1后; (3)vi /etc/sysconfig/network, hostname xxx; (4)修改防火墙设置,service iptables status, iptables -I INPUT -p tcp --dport 3306 -jACCEPT (SUZE:/usr/sbin/iptables, https://en.opensuse.org/Iptables),service iptables stop; (5)check password in GRANT, 'root'@'%' ? Q: log正常,但主库变更备库中不能同步得到 A: 检查my.cnf中的binlog-do-db、replicate-do-db Q: 如需要绕过权限检验 A: 可在配置文件(win: my.ini / linux: my.cnf)加上一句:skip-grant-tables Q: 不同安装文件有什么不同 A: In most cases, you only need to install the MySQL-server and MySQL-client packages to get a functional MySQL installation. MySQL-devel-VERSION.i386.rpm The libraries and include files that are needed if you want to compile other MySQL clients, such as the Perl modules. MySQL-shared-VERSION.i386.rpm This package contains the shared libraries (libmysqlclient.so*) that certain languages and applications need to dynamically load and use MySQL. MySQL-shared-compat-VERSION.i386.rpm This package includes the shared libraries for both MySQL 3.23 and MySQL 4.0. Install this package instead of MySQL-shared if you have applications installed that are dynamically linked against MySQL 3.23 but you want to upgrade to MySQL 4.0 without breaking the library dependencies. MySQL-embedded-VERSION.i386.rpm The embedded MySQL server library (from MySQL 4.0). Q: ./mysqld_multi --defaults-file=/home/xxx/my.cnf start 1,报错:mysqld_safe the file /usr/local/mysql/bin/mysqld does not exist or is not executable. A: 解决:去mysql当前路径,./mysqld_multi --defaults-file=my.cnf start 1,绝对路径有可能找不到。转载地址:http://xbili.baihongyu.com/