MySQL真實(shí)環(huán)境搭建主從
創(chuàng)新互聯(lián)-專業(yè)網(wǎng)站定制、快速模板網(wǎng)站建設(shè)、高性價(jià)比禹城網(wǎng)站開發(fā)、企業(yè)建站全套包干低至880元,成熟完善的模板庫,直接使用。一站式禹城網(wǎng)站制作公司更省心,省錢,快速模板網(wǎng)站建設(shè)找我們，業(yè)務(wù)覆蓋禹城地區(qū)。費(fèi)用合理售后完善，10年實(shí)體公司更值得信賴。
防偽碼：人之所以能，是相信能。
前言：當(dāng)今數(shù)據(jù)庫有oracle mysql SQL Server ACCESS 等等很多種，今天我們來真實(shí)環(huán)境搭建mysql主從。先說一下四種數(shù)據(jù)庫的區(qū)別：
1.四種數(shù)據(jù)庫的區(qū)別：
ACCESS：功能相對(duì)不是那么強(qiáng)大，主要是開發(fā)單機(jī)版軟件中經(jīng)常用到。
SQL Server：是目前應(yīng)用比較廣泛和普遍的一款數(shù)據(jù)庫，是數(shù)據(jù)庫發(fā)展的一個(gè)里程碑。
MySQL：是一個(gè)開源的關(guān)系數(shù)據(jù)庫管理系統(tǒng)，有快速、可靠和易于使用的特點(diǎn)；MySQL服務(wù)器工作在客戶/服務(wù)器或嵌入系統(tǒng)中。
Oracle：Oracle的功能比較強(qiáng)大，一般用于超大型管理系統(tǒng)軟件的建立，Oracle良好的兼容性、可移植性、可連接性和高生產(chǎn)率使Oracle RDBMS具有良好的開放性?，F(xiàn)在的應(yīng)用范圍也已經(jīng)比較廣泛。
下面讓我們來搭建主從：
操作系統(tǒng)centos6.5_64
數(shù)據(jù)庫版本mysql5.6.29
主數(shù)據(jù)庫 192.168.226.133
從數(shù)據(jù)庫192.168.226.134
mysql同步帳戶root/root
一．安裝mysql準(zhǔn)備
1、下載并上傳mysql源碼包mysql-5.6.29.tar.gz
http://mirrors.sohu.com/mysql/MySQL-5.6/mysql-5.6.29.tar.gz
2、檢查并卸載系統(tǒng)中低版本的mysql
rpm -qa |grep mysql
yum remove mysql mysql-server mysql-libs
.jpg)
3、安裝必要的系統(tǒng)插件
yum install ncurses-deve cmake
或者
rpm -ivhncurses-devel-5.7-3.20090208.el6.x86_64.rpm
rpm -ivhcmake-2.6.4-5.el6.x86_64.rpm
二、添加用戶和組:
添加用戶和組:
groupadd mysql
useradd -g mysql mysql -s /usr/sbin/nologin
mkdir -p /data/mysql/data
mkdir -p /data/mysql/log/iblog
mkdir -p /data/mysql/log/binlog
mkdir -p /data/mysql/log/relaylog
mkdir -p /data/mysql/run
mkdir -p /data/mysql/tmp
chown -R mysql:mysql /data/mysql
chmod -R 755 /data/mysql
.jpg)
1、解壓
tar zxfmysql-5.6.29.tar.gz
cd mysql-5.6.29
編譯安裝mysql：
cmake \
-DCMAKE_INSTALL_PREFIX=/data/mysql \
-DINSTALL_DATADIR=/data/mysql/data \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DEXTRA_CHARSETS=all \
-DWITH_SSL=yes \
-DWITH_EMBEDDED_SERVER=1 \
-DENABLED_LOCAL_INFILE=1 \
-DWITH_MYISAM_STORAGE_ENGINE=1 \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_ARCHIVE_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITH_FEDERATED_STORAGE_ENGINE=1 \
-DWITH_PARTITION_STORAGE_ENGINE=1 \
-DMYSQL_TCP_PORT=3306 \
-DENABLED_LOCAL_INFILE=1 \
-DSYSCONFDIR=/etc \
-DWITH_READLINE=on
.jpg)
make
make install
cp /data/mysql/support-files/my-default.cnf /data/mysql/my.cnf
修改主數(shù)據(jù)庫配置文件my.cnf
vi /data/mysql/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html
# *** DO NOT EDIT THIS FILE. It's a template which will be copied to the
# *** default location during install, and will be replaced if you
# *** upgrade to a newer version of MySQL.
[mysqld]
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
# These are commonly set, remove the # and set as required.
# basedir = .....
# datadir = .....
# port = .....
# server_id = .....
# socket = .....
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
[client]
port = 3306
socket =/data/mysql/run/mysql.sock
[mysql]
no-auto-rehash
[mysqld]
##enable autocommit
autocommit=1
general_log=off
explicit_defaults_for_timestamp=true
# system
basedir=/data/mysql
datadir=/data/mysql/data/
max_allowed_packet=134217728
max_connections=8192
max_user_connections=8000
open_files_limit=65535
pid_file=/data/mysql/run/mysqld.pid
port=3306
server_id=128
skip_name_resolve=ON
socket=/data/mysql/run/mysql.sock
tmpdir=/data/mysql/tmp
# binlog
binlog_cache_size=32768
binlog_format=row
expire_logs_days=15
log-bin=/data/mysql/log/binlog/master-bin
log-bin-index=/data/mysql/log/binlog/master-bin.index
log_slave_updates=ON
max_binlog_cache_size=2147483648
max_binlog_size=524288000
sync_binlog=100
#relay
# LOGGING #
log_error = /data/mysql/log/alert.log
log_queries_not_using_indexes = 1
slow_query_log = 1
slow_query_log_file = /data/mysql/log/slow.log
log_slave_updates=ON
log_slow_admin_statements=1
long_query_time=1
#slave#
slave_skip_errors=OFF
log_slave_updates=ON
# innodb #
innodb_log_group_home_dir=/data/mysql/log/iblog
innodb_data_home_dir=/data/mysql/log/iblog
innodb_adaptive_flushing=1
innodb_additional_mem_pool_size=20M
innodb_buffer_pool_instances=8
innodb_change_buffering=inserts
innodb_data_file_path=ibdata1:32M;ibdata2:16M:autoextend
innodb_flush_method = O_DIRECT
innodb_log_files_in_group = 4
innodb_log_file_size = 100M
innodb_flush_log_at_trx_commit = 1
innodb_file_per_table = 1
innodb_buffer_pool_size = 128M
innodb_file_format=Barracuda
innodb_file_io_threads=4
innodb_flush_neighbors=0
innodb_io_capacity=200
innodb_lock_wait_timeout=5
innodb_log_buffer_size=64M
innodb_lru_scan_depth=2048
innodb_max_dirty_pages_pct=60
innodb_old_blocks_time=1000
innodb_online_alter_log_max_size=200M
innodb_open_files=200
innodb_print_all_deadlocks=1
innodb_purge_threads=4
innodb_read_ahead_threshold=0
innodb_read_io_threads=8
innodb_rollback_on_timeout=0
innodb_sort_buffer_size=2M
innodb_spin_wait_delay=6
innodb_stats_on_metadata=0
innodb_strict_mode=1
innodb_sync_array_size=256
innodb_sync_spin_loops=30
innodb_thread_concurrency=64
innodb_use_native_aio=0
innodb_write_io_threads=8
innodb_support_xa=1
[mysqld_safe]
datadir=/data/mysql/data/
修改從數(shù)據(jù)庫my.cnf配置文件：
vi /data/mysql/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html
# *** DO NOT EDIT THIS FILE. It's a template which will be copied to the
# *** default location during install, and will be replaced if you
# *** upgrade to a newer version of MySQL.
[mysqld]
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
# These are commonly set, remove the # and set as required.
# basedir = .....
# datadir = .....
# port = .....
# server_id = .....
# socket = .....
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
[client]
port = 3306
socket =/data/mysql/run/mysql.sock
[mysql]
no-auto-rehash
[mysqld]
##enable autocommit
autocommit=1
general_log=off
explicit_defaults_for_timestamp=true
# system
basedir=/data/mysql
datadir=/data/mysql/data/
max_allowed_packet=134217728
max_connections=8192
max_user_connections=8000
open_files_limit=65535
pid_file=/data/mysql/run/mysqld.pid
port=3306
server_id=240
skip_name_resolve=ON
socket=/data/mysql/run/mysql.sock
tmpdir=/data/mysql/tmp
# binlog
binlog_cache_size=32768
binlog_format=row
expire_logs_days=15
log-bin=/data/mysql/log/binlog/slave-bin
log-bin-index=/data/mysql/log/binlog/slave-bin.index
log_slave_updates=ON
max_binlog_cache_size=2147483648
max_binlog_size=524288000
sync_binlog=100
#relay
relay-log=/data/mysql/log/relaylog/slave-relay-bin
relay-log-index=/data/mysql/log/relaylog/slave-relay-bin.index
# LOGGING #
log_error = /data/mysql/log/alert.log
log_queries_not_using_indexes = 1
slow_query_log = 1
slow_query_log_file = /data/mysql/log/slow.log
log_slave_updates=ON
log_slow_admin_statements=1
long_query_time=1
#slave#
slave_skip_errors=OFF
log_slave_updates=ON
# innodb #
innodb_log_group_home_dir=/data/mysql/log/iblog
innodb_data_home_dir=/data/mysql/log/iblog
innodb_adaptive_flushing=1
innodb_additional_mem_pool_size=20M
innodb_buffer_pool_instances=8
innodb_change_buffering=inserts
innodb_data_file_path=ibdata1:32M;ibdata2:16M:autoextend
innodb_flush_method = O_DIRECT
innodb_log_files_in_group = 4
innodb_log_file_size = 100M
innodb_flush_log_at_trx_commit = 1
innodb_file_per_table = 1
innodb_buffer_pool_size = 128M
innodb_file_format=Barracuda
innodb_file_io_threads=4
innodb_flush_neighbors=0
innodb_io_capacity=200
innodb_lock_wait_timeout=5
innodb_log_buffer_size=64M
innodb_lru_scan_depth=2048
innodb_max_dirty_pages_pct=60
innodb_old_blocks_time=1000
innodb_online_alter_log_max_size=200M
innodb_open_files=200
innodb_print_all_deadlocks=1
innodb_purge_threads=4
innodb_read_ahead_threshold=0
innodb_read_io_threads=8
innodb_rollback_on_timeout=0
innodb_sort_buffer_size=2M
innodb_spin_wait_delay=6
innodb_stats_on_metadata=0
innodb_strict_mode=1
innodb_sync_array_size=256
innodb_sync_spin_loops=30
innodb_thread_concurrency=64
innodb_use_native_aio=0
innodb_write_io_threads=8
innodb_support_xa=1
[mysqld_safe]
datadir=/data/mysql/data/
cd /data/mysql/
./scripts/mysql_install_db --defaults-file=/data/mysql/my.cnf --user=mysql --datadir=/data/mysql/data
vi /etc/profile
PATH=/data/mysql/bin:$PATH:/sbin
cp /data/mysql/my.cnf /etc/my.cnf
cp /data/mysql/support-files/mysql.server /etc/init.d/mysql
vi /etc/init.d/mysql
修改mysqld_pid_file_path=/data/mysql/run/mysqld.pid
.jpg)
chkconfig --add mysql
/etc/init.d/mysql start
/etc/init.d/mysql stop
.jpg)
主上做相應(yīng)的配置從上也做如上相應(yīng)的配置。
在主服務(wù)器上登陸數(shù)據(jù)庫，并配置數(shù)據(jù)庫
mysql -h227.0.0.1 -uroot -P3306
注意：如果
此報(bào)錯(cuò)。
原因: 這是由于系統(tǒng)默認(rèn)會(huì)查找/usr/bin下的命令，如果這個(gè)命令不在這個(gè)目錄下，當(dāng)然會(huì)找不到命令，我們需要做的就是映射一個(gè)鏈接到/usr/bin目錄下，相當(dāng)于建立一個(gè)鏈接文件。
首先得知道m(xù)ysql命令或mysqladmin命令的完整路徑，比如mysql的路徑是：/usr/local/mysql/bin/mysql，我們則可以這樣執(zhí)行命令：
ln -s /data/mysql/bin/mysql /usr/bin
即可解決問題。
mysql>set old_passwords=0;
mysql>GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'root' WITH GRANT OPTION;
mysql> SHOW MASTER STATUS;
+-------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
|master-bin.000003 | 333| | | |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
需要用到上面標(biāo)注紅色的兩個(gè)參數(shù)
在從服務(wù)器上登陸數(shù)據(jù)庫，并配置數(shù)據(jù)庫
mysql -h227.0.0.1 -uroot -P3306
mysql>set old_passwords=0;
mysql>GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'root' WITH GRANT OPTION;
mysql>stop slave;
mysql> CHANGE MASTER TO MASTER_HOST='10.140.19.189',MASTER_USER='root',MASTER_PASSWORD='root' ,MASTER_LOG_FILE='master-bin.000003',MASTER_LOG_POS=333;
mysql>start slave;
mysql>show slave status \G
在打印出來的狀態(tài)下，查看以下兩個(gè)參數(shù)的值：
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
如果這兩個(gè)參數(shù)的值都是yes，則運(yùn)行正常，可以在master數(shù)據(jù)庫上添加一個(gè)數(shù)據(jù)庫或者添加一張表，檢查slave數(shù)據(jù)庫上是否存在。
如果 這兩個(gè)參數(shù)有任何一個(gè)不是 Yes，則說明存在問題?？梢圆榭磗lave上的數(shù)據(jù)庫錯(cuò)誤日志文件查看錯(cuò)誤原因。
1、server-id一致。
2、用戶權(quán)限不夠。需要的權(quán)限包括：REPLICATION SLAVE,RELOAD,CREATE USER,SUPER。
GRANT REPLICATION SLAVE,RELOAD,CREATE USER,SUPER ON *.* TO {USER}@{IP_ADDR} WITH GRANT OPTION;
3、數(shù)據(jù)庫UUID一致，如果mysql安裝時(shí)是通過批量復(fù)制安裝的，則有可能數(shù)據(jù)庫的UUID一致，進(jìn)入數(shù)據(jù)庫的datadir目錄，修改auto.cnf。隨意修改下uuid中的值，重啟mysqld服務(wù)即可。
4、Master數(shù)據(jù)庫端口被防火墻阻擋。
希望能幫到大家！謝謝！
重慶分公司
重慶分公司
