二進位安裝MySQL資料庫

標籤：配置   res   圖片   環境變數   space   ima   ant   ror   mod   

今天安裝的是二進位的mysql包5.7.21的包，在設定檔的時候采了好多坑，左後還是搞定了，來和大家分享一下

 

 

 

二進位msyql5.7.21版本的主從複製安裝

 

建立/picclife目錄

mkdir  /picclife

建立/picclife/data 目錄

mkdir  /picclife/data

建立使用者和組 為mysql

groupadd  mysql

useradd   -g   mysql  mysql

將二進位mysql的包傳到/picclife的目錄下

解壓軟體包

 tar  -zxf 二進位包

改名

mv   解壓出來的包  mysql

加屬組屬主

chown  -R mysql:mysql  mysql

 

配置環境變數

vim  /etc/profile

 

mysql_home=/picclife/mysql

export PATH=$PATH:$mysql_home/bin

 

生效

source /etc/profile

 

編輯設定檔

vim /etc/my.cnf

[client]

port                           = 3306

socket                         = /tmp/mysql.sock

##default-character-set         = utf8

[mysql]

port                           = 3306

socket                         = /tmp/mysql.sock

#default-character-set          = utf8

[mysqld]

# GENERAL #

default_storage_engine         = InnoDB

#character-set-server           = utf8

#collation-server               = utf8_unicode_ci

basedir                        = /picclife/mysql

datadir                        = /picclife/data

socket                         = /tmp/mysql.sock

pid-file                       = /picclife/data/mysql.pid

port=3306

# SAFETY #

skip_name_resolve

max_allowed_packet             = 16M

max_connect_errors             = 100000

lower-case-table-names         = 1

# BINARY LOGGING #

server-id                      = 1             #server id

log_bin                        = mysql-bin   #開啟二進位日誌

relay_log                       = relay-bin    #開啟中級日誌

expire_logs_days               = 14

sync_binlog                    = 1

binlog_format                   = ROW

transaction_isolation           = READ-COMMITTED

relay_log_info_repository       = TABLE

master_info_repository          = TABLE

# REPLICATION #

gtid_mode                      = ON

enforce_gtid_consistency       = ON

log_slave_updates              = 1

#SEMI_SYNC

#rpl_semi_sync_master_enabled=1

#rpl_semi_sync_master_timeout=10000 # 1 second

# CACHES AND LIMITS #

tmp_table_size                 = 256M

max_heap_table_size            = 256M

query_cache_type               = 0

query_cache_size               = 0

max_connections                = 5000

thread_cache_size              = 1000

open_files_limit               = 65535

table_definition_cache         = 2048

table_open_cache               = 2048

sort_buffer_size               = 2M

sql_mode                       = NO_ENGINE_SUBSTITUTION

# INNODB #

innodb_flush_method            = O_DIRECT

innodb_log_files_in_group      = 2

innodb_log_file_size           = 256M

innodb_flush_log_at_trx_commit = 1

innodb_file_per_table          = 1

innodb_buffer_pool_size        = 10G

innodb_stats_on_metadata       = 0

innodb_buffer_pool_instances   = 4

# LOGGING #

log_error                      = /picclife/data/mysql-error.log

#log_queries_not_using_indexes  = 1

slow_query_log                 = 1

slow_query_log_file            = /picclife/data/mysql-slow.log

long_query_time                = 2

log_error_verbosity=2

wait_timeout = 7200

[mysqldump]

user=root

password=123456

 

安裝資料庫

./bin/mysqld --initialize --user=mysql --basedir=/picclfie/mysql --datadir=/picclife/data --innodb_undo_tablespaces=3 --explicit_defaults_for_timestamp

 

建立開機檔案

cp  /picclife/mysql/support-files/mysql.server /etc/init.d/mysqld

 

修改/etc/init.d/mysql

 

 

 

 加入到開機啟動項

chkconfig --add mysqld

設定開機啟動

chkconfig mysqld on

開啟mysql

service mysqld start

啟動也可以

(

資料庫常規啟停

mysqladmin -uroot -proot shutdown

mysqld_safe --defaults-file=/etc/my.cnf &

ps -ef|grep mysql

)

 

 

查看預設密碼

grep -i password /picclife/data/mysql-error.log

用初始化密碼登陸資料庫修改密碼

mysql -uroot -p查到的密碼

修改密碼

SET PASSWORD=PASSWORD(‘密碼‘);

flush privileges;

然後推出可以用新改的密碼登陸了

主的設定檔 已經修改好了，進入資料庫授權

給從伺服器slave複製的許可權：

grant  replication  slave  on *.* to  [email protected]”從的ip”  identified  by  “123”;

重新整理許可權：

glush    privileges;

查看主伺服器master的狀態已得到二進位的名和位置：

show   master  status；

 

主就配置好了開始配置從：

從的二進位安裝mysql和住的一樣

就是設定檔裡的server  id  不能衝突  可以開啟二進位日誌也可以不開啟，但必須開啟中繼日誌relay_log = mysql-relay

vim /etc/my.cnf

[client]

port                           = 3306

socket                         = /tmp/mysql.sock

##default-character-set         = utf8

[mysql]

port                           = 3306

socket                         = /tmp/mysql.sock

#default-character-set          = utf8

[mysqld]

# GENERAL #

default_storage_engine         = InnoDB

#character-set-server           = utf8

#collation-server               = utf8_unicode_ci

basedir                        = /picclife/mysql

datadir                        = /picclife/data

socket                         = /tmp/mysql.sock

pid-file                       = /picclife/data/mysql.pid

port=3306

# SAFETY #

skip_name_resolve

max_allowed_packet             = 16M

max_connect_errors             = 100000

lower-case-table-names         = 1

# BINARY LOGGING #

server-id                      = 2            #server id

log_bin                        = mysql-bin   #開啟二進位日誌

relay_log                       = relay-bin    #開啟中級日誌

expire_logs_days               = 14

sync_binlog                    = 1

binlog_format                   = ROW

transaction_isolation           = READ-COMMITTED

relay_log_info_repository       = TABLE

master_info_repository          = TABLE

# REPLICATION #

gtid_mode                      = ON

enforce_gtid_consistency       = ON

log_slave_updates              = 1

#SEMI_SYNC

#rpl_semi_sync_master_enabled=1

#rpl_semi_sync_master_timeout=10000 # 1 second

# CACHES AND LIMITS #

tmp_table_size                 = 256M

max_heap_table_size            = 256M

query_cache_type               = 0

query_cache_size               = 0

max_connections                = 5000

thread_cache_size              = 1000

open_files_limit               = 65535

table_definition_cache         = 2048

table_open_cache               = 2048

sort_buffer_size               = 2M

sql_mode                       = NO_ENGINE_SUBSTITUTION

# INNODB #

innodb_flush_method            = O_DIRECT

innodb_log_files_in_group      = 2

innodb_log_file_size           = 256M

innodb_flush_log_at_trx_commit = 1

innodb_file_per_table          = 1

innodb_buffer_pool_size        = 10G

innodb_stats_on_metadata       = 0

innodb_buffer_pool_instances   = 4

# LOGGING #

log_error                      = /picclife/data/mysql-error.log

#log_queries_not_using_indexes  = 1

slow_query_log                 = 1

slow_query_log_file            = /picclife/data/mysql-slow.log

long_query_time                = 2

log_error_verbosity=2

wait_timeout = 7200

其餘的按爪功你安裝和主的一樣

進入資料庫

進入資料庫：

關閉slave:

stop   slave;

設定master的host   user  二進位的名字位置等：

change   master  to  master_host=”主的ip”,master_user=”tom”,mster_password=”123”,

master_log_file=”mysql-bin.000002”,master_log_pos=106;

開啟slave

 start  slave；

查看slave的狀態：

 

 

現在主從複製完成，可以在主上建立庫。從上查看

 

注意：如果以上設定檔啟動報錯，就使用最下面的配置

 

 

 

 

 

 

 

二進位mysql安裝5.7.20的設定檔

vim /etc/my.cnf

 

[client]

port = 3306

socket = /tmp/mysql.sock

[mysqld]

server_id=1

port = 3306

user = mysql

character-set-server = utf8mb4

default_storage_engine = innodb

log_timestamps = SYSTEM

socket = /tmp/mysql.sock

basedir = /picclife/mysql

datadir = /picclife/data

pid-file = /picclife/data/mysql.pid

max_connections = 5000

max_connect_errors = 10000

table_open_cache = 2048

max_allowed_packet = 16M

open_files_limit = 65535

####====================================[innodb]==============================

innodb_buffer_pool_size = 10G

innodb_file_per_table = 1

innodb_write_io_threads = 4

innodb_read_io_threads = 4

innodb_purge_threads = 2

innodb_flush_log_at_trx_commit = 1

innodb_log_file_size = 512M

innodb_log_files_in_group = 2

innodb_log_buffer_size = 16M

innodb_max_dirty_pages_pct = 80

innodb_lock_wait_timeout = 30

innodb_data_file_path=ibdata1:1024M:autoextend

innodb_undo_tablespaces=3

#####====================================[log]==============================

log_error = /picclife/data/mysql-error.log

slow_query_log = 1

long_query_time = 2

slow_query_log_file = /picclife/data/mysql-slow.log

sql_mode=NO_ENGINE_SUBSTITUTION

 

二進位安裝MySQL資料庫