成人午夜视频全免费观看高清-秋霞福利视频一区二区三区-国产精品久久久久电影小说-亚洲不卡区三一区三区一区

mysql真實環(huán)境搭建主從-創(chuàng)新互聯(lián)

                        mysql真實環(huán)境搭建主從

站在用戶的角度思考問題，與客戶深入溝通，找到陵水黎族網(wǎng)站設(shè)計與陵水黎族網(wǎng)站推廣的解決方案，憑借多年的經(jīng)驗，讓設(shè)計與互聯(lián)網(wǎng)技術(shù)結(jié)合，創(chuàng)造個性化、用戶體驗好的作品，建站類型包括：網(wǎng)站制作、成都網(wǎng)站制作、企業(yè)官網(wǎng)、英文網(wǎng)站、手機端網(wǎng)站、網(wǎng)站推廣、域名注冊、雅安服務(wù)器托管、企業(yè)郵箱。業(yè)務(wù)覆蓋陵水黎族地區(qū)。

防偽碼：人之所以能，是相信能。

前言：當今數(shù)據(jù)庫有oracle mysql  SQL Server ACCESS 等等很多種，今天我們來真實環(huán)境搭建mysql主從。先說一下四種數(shù)據(jù)庫的區(qū)別：

1.四種數(shù)據(jù)庫的區(qū)別：

ACCESS：功能相對不是那么強大，主要是開發(fā)單機版軟件中經(jīng)常用到。

SQL Server：是目前應(yīng)用比較廣泛和普遍的一款數(shù)據(jù)庫，是數(shù)據(jù)庫發(fā)展的一個里程碑。

MySQL：是一個開源的關(guān)系數(shù)據(jù)庫管理系統(tǒng)，有快速、可靠和易于使用的特點；MySQL服務(wù)器工作在客戶/服務(wù)器或嵌入系統(tǒng)中。

Oracle：Oracle的功能比較強大，一般用于超大型管理系統(tǒng)軟件的建立，Oracle良好的兼容性、可移植性、可連接性和高生產(chǎn)率使Oracle RDBMS具有良好的開放性。現(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準備

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

mysql真實環(huán)境搭建主從

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

mysql真實環(huán)境搭建主從

二、添加用戶和組:

添加用戶和組:

groupadd  mysql

useradd -g mysql mysql -s /usr/sbin/nologin

mysql真實環(huán)境搭建主從

三、創(chuàng)建目錄及授權(quán)

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

mysql真實環(huán)境搭建主從

四、開始編譯

1、解壓

tar zxfmysql-5.6.29.tar.gz

cd mysql-5.6.29

mysql真實環(huán)境搭建主從

編譯安裝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

mysql真實環(huán)境搭建主從

make

make install

mysql真實環(huán)境搭建主從

五、修改配置文件my.cnf參數(shù)

cp /data/mysql/support-files/my-default.cnf  /data/mysql/my.cnf

mysql真實環(huán)境搭建主從

修改主數(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/

六、執(zhí)行MySQL安裝腳本

cd /data/mysql/

./scripts/mysql_install_db  --defaults-file=/data/mysql/my.cnf  --user=mysql --datadir=/data/mysql/data

七、修改系統(tǒng)環(huán)境變量

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

mysql真實環(huán)境搭建主從

mysql真實環(huán)境搭建主從

mysql真實環(huán)境搭建主從

chkconfig --add mysql

/etc/init.d/mysql start

/etc/init.d/mysql stop

mysql真實環(huán)境搭建主從

主上做相應(yīng)的配置從上也做如上相應(yīng)的配置。

九、配置主從

在主服務(wù)器上登陸數(shù)據(jù)庫，并配置數(shù)據(jù)庫

mysql -h227.0.0.1 -uroot -P3306

注意：如果mysql真實環(huán)境搭建主從此報錯。

原因: 這是由于系統(tǒng)默認會查找/usr/bin下的命令，如果這個命令不在這個目錄下，當然會找不到命令，我們需要做的就是映射一個鏈接到/usr/bin目錄下，相當于建立一個鏈接文件。

首先得知道m(xù)ysql命令或mysqladmin命令的完整路徑，比如mysql的路徑是：/usr/local/mysql/bin/mysql，我們則可以這樣執(zhí)行命令：

ln  -s  /data/mysql/bin/mysql /usr/bin

即可解決問題。

mysql真實環(huán)境搭建主從

mysql>set old_passwords=0;

mysql>GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'root' WITH GRANT OPTION;

mysql真實環(huán)境搭建主從

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)

需要用到上面標注紅色的兩個參數(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)下，查看以下兩個參數(shù)的值：

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

如果這兩個參數(shù)的值都是yes，則運行正常，可以在master數(shù)據(jù)庫上添加一個數(shù)據(jù)庫或者添加一張表，檢查slave數(shù)據(jù)庫上是否存在。

如果 這兩個參數(shù)有任何一個不是 Yes，則說明存在問題?？梢圆榭磗lave上的數(shù)據(jù)庫錯誤日志文件查看錯誤原因。

十、常見的錯誤原因

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ù)據(jù)庫的UUID一致，進入數(shù)據(jù)庫的datadir目錄，修改auto.cnf。隨意修改下uuid中的值，重啟mysqld服務(wù)即可。

4、Master數(shù)據(jù)庫端口被防火墻阻擋。

 希望能幫到大家！謝謝！

另外有需要云服務(wù)器可以了解下創(chuàng)新互聯(lián)cdcxhl.cn，海內(nèi)外云服務(wù)器15元起步，三天無理由+7*72小時售后在線，公司持有idc許可證，提供“云服務(wù)器、裸金屬服務(wù)器、高防服務(wù)器、香港服務(wù)器、美國服務(wù)器、虛擬主機、免備案服務(wù)器”等云主機租用服務(wù)以及企業(yè)上云的綜合解決方案，具有“安全穩(wěn)定、簡單易用、服務(wù)可用性高、性價比高”等特點與優(yōu)勢，專為企業(yè)上云打造定制，能夠滿足用戶豐富、多元化的應(yīng)用場景需求。

名稱欄目：mysql真實環(huán)境搭建主從-創(chuàng)新互聯(lián)
標題網(wǎng)址：http://jinyejixie.com/article2/gheic.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供網(wǎng)站設(shè)計公司、移動網(wǎng)站建設(shè)、關(guān)鍵詞優(yōu)化、標簽優(yōu)化、網(wǎng)站建設(shè)、用戶體驗

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)

成都網(wǎng)站建設(shè)
江山市| 屯昌县| 内丘县| 沙河市| 甘谷县| 开阳县| 嘉义市| 本溪市| 井陉县| 高州市| 天台县| 南城县| 澄江县| 池州市| 古丈县| 婺源县| 宜丰县| 普兰店市| 中山市| 乌海市| 霍林郭勒市| 罗平县| 八宿县| 盐亭县| 余干县| 东乡| 延安市| 竹溪县| 贡山| 仁化县| 额尔古纳市| 图木舒克市| 中山市| 台北市| 军事| 浦县| 青海省| 临汾市| 三河市| 绥芬河市| 加查县|