mysql5.7 部署和安装

下载mysql5.7：https://cdn.mysql.com//Downloads/MySQL-5.7/mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz

解压：tar zvxf mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz

转移目录：mv mysql-5.7.22-linux-glibc2.12-x86_64 /home/mysql

新建数据目录：mkdir /home/mysql/data

新建日志目录：mkdir /home/mysql/logs

新建日志目录：mkdir /home/mysql/tmp

 

新建启动目录：mkdir /var/lib/mysql

 

先替换/etc/my.cnf

安装①：bin/mysqld --defaults-file=/etc/my.cnf --initialize --user=root --basedir=/home/mysql --datadir=/home/mysql/data

######执行完后生存的root@localhost: t#=sUjf1ohNf这串密码需要记住

安装②：bin/mysql_ssl_rsa_setup  --datadir=/home/mysql/data

修改系统配置文件:

cd /home/mysql/support-files

cp mysql.server /etc/init.d/mysql

vi /etc/init.d/mysql

basedir=/home/mysql

datadir=/home/mysql/data

修改mf.cnf配置文件：参考按最下面的附录

启动：service mysql start

# /etc/init.d/mysql start

登陆：mysql -uroot -p

--如果出现：-bash: mysql: command not found

--就执行：ln -s /home/mysql/bin/mysql /usr/bin --没有出现就不用执行

--输入上面生成的临时密码

修改密码：mysql> set password=password('密码');

修改root的远程权限：mysql> grant all privileges on *.* to 'root'@'%' identified by '密码';

刷新：mysql> flush privileges;

添加系统路径：

# vi /etc/profile

export PATH=/home/mysql/bin:$PATH

加载系统路径：source /etc/profile

配置mysql自动启动：

chmod 755 /etc/init.d/mysql

chkconfig --add mysql

chkconfig --level 345 mysql on

补充：

--退出mysql命令窗口

#exit（或者quit）

 --查看mysql状态

#service mysql status

--停止mysql

#service mysql stop

--启动mysql

#service mysql start

##跳过密码校验启动

bin/mysqld_safe --skip-grant-tables

##修改root密码

update mysql.user set authentication_string = password('a123456') where user='root' and Host = 'localhost';

##创建用户

 CREATE USER 'vlr'@'%' IDENTIFIED BY 'cqt@1234';

 grant all privileges on *.* to 'vlr'@'%' identified by '121212f';

 

附my.cnf（其中一个service-id= 32128 的配置是根据IPip设置的ID）

my.cnf---------------------------------

[client]                                                       
port            = 3300                                         
socket          = /var/lib/mysql/mysql.sock                              
 
[mysql]                                                        
no-auto-rehash                                                 
max_allowed_packet = 128M                                      
#pager="less -i -n -S"                                         
#tee=/opt/mysql/query.log                                      
prompt                         = '(product)\u@\h [\d]> '       
default-character-set=utf8    
 
# The MySQL server                                             
[mysqld]               
# misc
sql_mode=NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION                                                        
port            = 3300                                         
user            = root
basedir         = /home/mysql                             
datadir         = /home/mysql/data               
tmpdir          = /home/mysql/tmp                
socket          = /var/lib/mysql/mysql.sock                              
symbolic-links = 0                                             
max_allowed_packet = 32M  
skip_name_resolve = 1                                        

#timeout                                                       
interactive_timeout = 600                                      
wait_timeout = 600                                             
connect_timeout = 20                                           
slave-net-timeout=30                                           
 
server-id       =32128                                       
 
#character set                                                 
character-set-server = utf8                                    
lower_case_table_names=1                                       
 
#timeout                                                       
wait_timeout=100                                               
interactive_timeout=100                                        
 
open_files_limit = 65535                                       
back_log = 1024                                                 
max_connections=1100                                           
max_user_connections=1000                                      
max_connect_errors = 1000000                                   
external-locking = FALSE                                          
skip-name-resolve                                              
event_scheduler=1                                              
log_timestamps = system                                             
 
#logs                                                          
log-output="FILE"                                              
slow_query_log = 1                                             
slow-query-log-file = /home/mysql/logs/slow.log                                 
log-error   = /home/mysql/logs/error.log                                        
log_error_verbosity = 2                                        
pid-file = mysql.pid                                           
long_query_time=1  
slow_query_log = on                                            
log-slow-slave-statements = 1
#log_slow_verbosity = "full"                                    
#log-queries-not-using-indexes = 1                             
#log_throttle_queries_not_using_indexes = 10                   
explicit_defaults_for_timestamp = 1                            
default-time-zone = '+8:00'
log-slow-admin-statements = 1                                  
 
#binlog                                                        
binlog_format = row                                            
log-bin     = /home/mysql/logs/mysql-bin         
binlog_cache_size = 1M                                         
max_binlog_cache_size = 2G                                     
max_binlog_size = 1G                                           
expire-logs-days = 7                                           
 
#procedure                                                   
log_bin_trust_function_creators = 1                            
 
#gtid                                                          
gtid_mode = on
enforce_gtid_consistency = 1                                 
 
#relay log                                                     
#skip_slave_start = 1                                          
#slave-skip-errors=all                                          
slave-skip-errors=1062,1050,1051,1146,1145,1061,1032,1304,1396,1537,1539
max_relay_log_size = 1G                                        
relay_log_purge = 1                                            
relay_log_recovery = 1                                         
#slave_parallel_workers = 2                                    
master_verify_checksum = 1                                     
master_info_repository = TABLE
relay_log_info_repository = TABLE                          
slave_sql_verify_checksum = 1                                  
slave_allow_batching = 1                                       
log_slave_updates
slave-rows-search-algorithms = 'INDEX_SCAN,HASH_SCAN'
 
#buffers & cache                                               
table_open_cache = 2048                                        
table_definition_cache = 2048
table_open_cache_instances = 64                                 
sort_buffer_size = 4M
join_buffer_size = 4M                                         
thread_cache_size = 768                                        
query_cache_size = 0                                           
query_cache_type = 0                                           
query_cache_limit = 256K                                       
query_cache_min_res_unit = 512                                 
thread_stack = 512K                                            
key_buffer_size = 32M                                          
read_buffer_size = 8M                                          
read_rnd_buffer_size = 4M                                      
bulk_insert_buffer_size = 64M                                  
tmp_table_size = 96M                                           
max_heap_table_size = 96M                                      
 
#myisam                                                        
myisam_sort_buffer_size = 128M                                 
myisam_max_sort_file_size = 10G                                
myisam_repair_threads = 1  
lock_wait_timeout = 3600                                    
   
#innodb                                                        
innodb_data_home_dir = /home/mysql/data          
innodb_log_group_home_dir = /home/mysql/logs     
innodb_data_file_path = ibdata1:1G:autoextend                  
innodb_buffer_pool_size = 45G                                   
#innodb_buffer_pool_instances = 8                              
innodb_spin_wait_delay = 30                                    
sync_binlog=1                                                  
innodb_flush_log_at_trx_commit = 1                             
innodb_log_buffer_size = 64M                                   
innodb_log_file_size = 256M                                    
innodb_log_files_in_group = 3                                  
innodb_max_dirty_pages_pct = 50                                
innodb_lock_wait_timeout = 10                                 
innodb_file_per_table = 1                                      
innodb_rollback_on_timeout= 1  
innodb_print_all_deadlocks = 1                                
innodb_status_file = 1                                         
innodb_read_io_threads = 16                                    
innodb_write_io_threads = 16                                   
innodb_io_capacity = 300                                    
#innodb_max_io_capacity = 20000   
innodb_flush_neighbors = 1                              
innodb_adaptive_flushing = 1                                   
innodb_flush_method = O_DIRECT       
innodb_lru_scan_depth = 4000
innodb_checksum_algorithm = crc32                          
transaction_isolation = READ-COMMITTED
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1                        
 
innodb_sync_spin_loops = 100                                    
#innodb_support_xa = 0                                         
innodb_thread_concurrency = 0                                  
innodb_thread_sleep_delay = 500                                
#innodb_file_io_threads    = 4                                 
innodb_concurrency_tickets = 1000                              
#innodb_file_format = Barracuda                                
innodb_purge_threads=4
innodb_page_cleaners = 4
innodb_open_files = 65535                                         
innodb_purge_batch_size = 32                                   
innodb_old_blocks_pct=75                                       
innodb_change_buffering=all   
innodb_online_alter_log_max_size = 4G                                 
internal_tmp_disk_storage_engine = InnoDB
innodb_stats_on_metadata = 0                   
 
innodb_max_undo_log_size=4G    

innodb_status_output = 0
innodb_status_output_locks = 0                        
 
#performance_schema
performance_schema = 1
performance_schema_instrument = '%=on'

#innodb monitor
innodb_monitor_enable="module_innodb"
innodb_monitor_enable="module_server"
innodb_monitor_enable="module_dml"
innodb_monitor_enable="module_ddl"
innodb_monitor_enable="module_trx"
innodb_monitor_enable="module_os"
innodb_monitor_enable="module_purge"
innodb_monitor_enable="module_log"
innodb_monitor_enable="module_lock"
innodb_monitor_enable="module_buffer"
innodb_monitor_enable="module_index"
innodb_monitor_enable="module_ibuf_system"
innodb_monitor_enable="module_buffer_page"
innodb_monitor_enable="module_adaptive_hash"
      

#semi replication
#plugin_load = "validate_password.so;rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
rpl_semi_sync_master_enabled=1
rpl_semi_sync_master_timeout=3000 # 3000 second
rpl_semi_sync_slave_enabled=1
rpl_semi_sync_master_wait_for_slave_count=1      

#group relplication
binlog_group_commit_sync_delay=10
binlog_group_commit_sync_no_delay_count=20
                   
#parallel write
slave_parallel_type=LOGICAL_CLOCK
slave_parallel_workers=16
 
# FOR Percona 5.7                                              
#thread_handling = pool-of-threads                              
#thread_pool_oversubscribe = 16                                 
#extra_port = 33306                                             
#extra_max_connections = 2                                      
 
[mysqldump]                                                    
quick                                                          
max_allowed_packet = 128M                                      
#myisam_max_sort_file_size = 10G                               
 
[mysqld_safe]                                                  
malloc-lib=  /usr/lib64/libjemalloc.so                         
   
[mysqld_multi]                                                 
mysqld = /home/mysql/bin/mysqld_safe                      
mysqladmin = /home/mysql/bin//mysqladmin                  
log = /home/data/mysqld_multi.log          

-------------------------------------------

###配置集群模式

进入mysql(ql(ip从服务器从服务器)执行以下语句：

先使用以下语句查看主的log信息

show master status;

 

//双主即两个都执行change对方地址

 

change master to

master_host='10.10.1.12',

master_user='root',

master_password='xxxxxxx',

master_port=3300,

master_log_file='mysql-bin.000003',

master_log_pos=153225199;

9)启动slave：start slave;

10)查看slave状态：show slave status\G

注：其中Slave_IO_Running 与 Slave_SQL_Running 的值都必须为YES，才表明状态正常。

3、测试：

在主节点创建数据库，插入数据，在从节点查看是否同步出现。（略）

 

至此，主从同步搭建完毕！

##两台都执行

start slave;

show slave status\G;

查看主同步文件及pos

show master status;