王衍 操作记录

[spacehuman]

机器：179 操作：安装mysql5.6.35二进制已编译版，配置主从

一、安装mysql 1、下载mysql二进制编译包到/usr/local/src目录下

2、解压压缩包并移动到/usr/local目录下，重命名为mysql

3、创建mysql用户 useradd -s /sbin/nologin mysql -M

4、创建相关目录，并修改目录属主和属组 mkdir -p /data/mysql chown mysql. /data/mysql

5、初始化mysql，并解决初始化过程中出现的问题 /usr/local/mysql/scripts/mysql_install_db --user=mysql --datadir=/data/mysql

6、复制mysql配置文件和启动脚本到合适的地方，并修改启动脚本的权限，设置开机启动 cp /usr/local/mysql/support-files/my-default.cnf /etc/my.cnf cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld chmod 755 /etc/init.d/mysqld chkconfig mysqld on

7、修改/etc/my.cnf文件，改为以下配置，开启主从，慢查询相关的支持

[mysqld] port = 3306 server-id = 179 skip-name-resolve back_log = 600 max_connections = 1000 max_connect_errors = 6000 open_files_limit = 65535 table_open_cache = 128 max_allowed_packet = 4M binlog_cache_size = 1M max_heap_table_size = 8M tmp_table_size = 16M read_buffer_size = 2M read_rnd_buffer_size = 8M sort_buffer_size = 8M join_buffer_size = 8M key_buffer_size = 4M thread_cache_size = 8 query_cache_size = 8M query_cache_limit = 2M ft_min_word_len = 4 log_bin = mysql-bin binlog_format = mixed expire_logs_days = 30 log_error = /data/mysql/mysql-error.log slow_query_log = 1 long_query_time = 1 slow_query_log_file = /data/mysql/mysql-slow.log performance_schema = 0 explicit_defaults_for_timestamp skip-external-locking default_storage_engine = InnoDB innodb_file_per_table = 1 innodb_open_files = 500 innodb_buffer_pool_size = 64M innodb_write_io_threads = 4 innodb_read_io_threads = 4 innodb_thread_concurrency = 0 innodb_purge_threads = 1 innodb_flush_log_at_trx_commit = 2 innodb_log_buffer_size = 2M innodb_log_file_size = 32M innodb_log_files_in_group = 3 innodb_max_dirty_pages_pct = 90 innodb_lock_wait_timeout = 120 bulk_insert_buffer_size = 8M myisam_sort_buffer_size = 8M myisam_max_sort_file_size = 10G myisam_repair_threads = 1 interactive_timeout = 28800 wait_timeout = 28800 [mysqldump] quick max_allowed_packet = 16M [myisamchk] key_buffer_size = 8M sort_buffer_size = 8M read_buffer = 4M write_buffer = 4M

8、修改mysql启动脚本，添加basedir和datadir的路径

9、启动mysql，完成mysql安装 /etc/init.d/mysqld start

二、配置主从结构的主 1、进入mysql命令界面

2、输入以下授权语句 grant replication slave on *.* to 'repl'@'192.168.14.180' identified by 'asd123asd';

3、锁表操作没有做，因为是新机器，没有其他用户对数据库的操作，所以跳过锁表

4、查询binlog相关信息，用于从中的配置 show master status;

5、到此基本完成主的配置

机器：180 操作：安装mysql5.6.35二进制已编译版，配置主从

一、安装mysql 1、下载mysql二进制编译包到/usr/local/src目录下

2、解压压缩包并移动到/usr/local目录下，重命名为mysql

3、创建mysql用户 useradd -s /sbin/nologin mysql -M

4、创建相关目录，并修改目录属主和属组 mkdir -p /data/mysql chown mysql. /data/mysql

5、初始化mysql，并解决初始化过程中出现的问题 /usr/local/mysql/scripts/mysql_install_db --user=mysql --datadir=/data/mysql

6、复制mysql配置文件和启动脚本到合适的地方，并修改启动脚本的权限，设置开机启动 cp /usr/local/mysql/support-files/my-default.cnf /etc/my.cnf cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld chmod 755 /etc/init.d/mysqld chkconfig mysqld on

7、修改/etc/my.cnf文件，改为以下配置，开启主从，慢查询相关的支持

[mysqld] port = 3306 server-id = 180 skip-name-resolve back_log = 600 max_connections = 1000 max_connect_errors = 6000 open_files_limit = 65535 table_open_cache = 128 max_allowed_packet = 4M max_heap_table_size = 8M tmp_table_size = 16M read_buffer_size = 2M read_rnd_buffer_size = 8M sort_buffer_size = 8M join_buffer_size = 8M key_buffer_size = 4M thread_cache_size = 8 query_cache_size = 8M query_cache_limit = 2M ft_min_word_len = 4 expire_logs_days = 30 log_error = /data/mysql/mysql-error.log slow_query_log = 1 long_query_time = 1 slow_query_log_file = /data/mysql/mysql-slow.log performance_schema = 0 explicit_defaults_for_timestamp skip-external-locking default_storage_engine = InnoDB innodb_file_per_table = 1 innodb_open_files = 500 innodb_buffer_pool_size = 64M innodb_write_io_threads = 4 innodb_read_io_threads = 4 innodb_thread_concurrency = 0 innodb_purge_threads = 1 innodb_flush_log_at_trx_commit = 2 innodb_log_buffer_size = 2M innodb_log_file_size = 32M innodb_log_files_in_group = 3 innodb_max_dirty_pages_pct = 90 innodb_lock_wait_timeout = 120 bulk_insert_buffer_size = 8M myisam_sort_buffer_size = 8M myisam_max_sort_file_size = 10G myisam_repair_threads = 1 interactive_timeout = 28800 wait_timeout = 28800 [mysqldump] quick max_allowed_packet = 16M [myisamchk] key_buffer_size = 8M sort_buffer_size = 8M read_buffer = 4M write_buffer = 4M

8、修改mysql启动脚本，添加basedir和datadir的路径

9、启动mysql，完成mysql安装 /etc/init.d/mysqld start

二、配置主从结构的从 1、进入mysql命令界面

2、暂停从库 stop slave;

3、输入并运行以下语句，建立主从关系 change master to master_host='192.168.14.179', master_port=3306, master_user='repl', master_password='asd123asd', master_log_file='mysql-bin.000003', master_log_pos=331;

4、开启从库 start slave;

5、从库中查看同步情况，看是否有问题，没有问题，主从结构建立成功 show slave status\G