mysql编译安装主从复制

1.安装相关依赖

yum install cmake -y
yum install gcc gcc-c++ -y
yum install ncurses-devel -y

2.下载mysql并安装
    

wget http://archive.mariadb.org//mariadb-5.5.42/source/mariadb-5.5.42.tar.gz

     创建mysql用户和组
    

groupadd mysql
useradd mysql -s /sbin/nologin -M -g mysql

     解压mysql
    

tar xf mariadb-5.5.42.tar.gz

     进入解压目录
    

cd mariadb-5.5.42.tar.gz

     执行cmake 编译

cmake . -DCMAKE_INSTALL_PREFIX=/application/mariadb-5.5.42 \
-DMYSQL_DATADIR=/data \
-DMYSQL_UNIX_ADDR=/data/mysql.sock \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DEXTRA_CHARSETS=gbk,gb2312,utf8,ascii \
-DENABLED_LOCAL_INFILE=ON \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_FEDERATED_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITHOUT_EXAMPLE_STORAGE_ENGINE=1 \
-DWITHOUT_PARTITION_STORAGE_ENGINE=1 \
-DWITH_FAST_MUTEXES=1 \
-DWITH_ZLIB=bundled \
-DENABLED_LOCAL_INFILE=1 \
-DWITH_READLINE=1 \
-DWITH_EMBEDDED_SERVER=1 \
-DWITH_DEBUG=0

make && make install

软连接

ln -s /application/mariadb-5.5.42 /application/mysql

cp最小的配置文件

cp mariadb-5.5.42/support-files/my-small.cnf /etc/my.cnf

配置环境变量

echo 'export PATH=/application/mysql/bin:$PATH' >>/etc/profile
 tail -l /etc/profile
 source /etc/profile
 echo $PATH

给目录权限

chown -R mysql.mysql /application/mysql
 chmod -R 1777 /tmp/

初始化

cd /application/mysql/scripts/
 ./mysql_install_db --basedir=/application/mysql/ --datadir=/data/ --user=mysql

cp启动脚本

cd /tools/mariadb-5.5.42
 cp support-files/mysql.server /etc/init.d/mysqld
 chmod +x /etc/init.d/mysqld
 /etc/init.d/mysqld start
 netstat -lntup |grep 3306

简单优化

mysql 
     select user,host from mysql.user;
     delete from mysql.user where user='';
     delete from mysql.user where host='testdb';   //主机名
    delete from mysql.user where host='::1';   //ipv6
    drop database test;
     show databases;

    grant all on *.* to root@'192.168.200.%' identified by 'password123';

    
添加额外管理员(非必要)
 

mysql>delete from mysql.user;
mysql>grant all privileges on *.* to system@localhost identified by 'password123' with grant option;

给root用户添加密码:

/application/mysql/bin/mysqladmin -u root password 'password123'

加入启动

chkconfig mysqld on

mysql  主重复制

修改cnf文件

vim /etc/my.cnf

详情my.cnf配置文件

主:my.cnf

[client]
 port        = 3306
 socket        = /data/mysql.sock

[mysql]
 no-auto-rehash

[mysqld]
 port        = 3306
 socket        = /data/mysql.sock
 basedir     = /application/mysql
 datadir        = /data
 open_files_limit    = 1024
 back_log = 600
 max_connections = 800
 max_connect_errors = 3000
 table_cache = 1024
 external-locking = FALSE
max_allowed_packet =16M
 sort_buffer_size = 1M
 join_buffer_size = 1M
 thread_cache_size = 100
 thread_concurrency = 8
 query_cache_size = 2M
 query_cache_limit = 1M
 query_cache_min_res_unit = 2k
 #default_table_type = InnoDB
 thread_stack = 192K
 #transaction_isolation = READ-COMMITTED
 tmp_table_size = 2M
 max_heap_table_size = 2M
 long_query_time = 1
 #log_long_format#log-error = /application/mysql/data/error.log
 #log-slow-queries = /application/mysql/data/slow.log
 pid-file = /application/mysql/data/mysql.pid
 log-bin = /application/mysql/data/mysql-bin
 relay-log = /application/mysql/data/relay-bin
 relay-log-info-file = /application/mysql/data/relay-log.info
 binlog_cache_size = 1M
 max_binlog_cache_size = 1M
 max_binlog_size = 2M
 expire_logs_days = 7
 key_buffer_size = 16M
 read_buffer_size = 1M
 read_rnd_buffer_size = 1M
 bulk_insert_buffer_size = 1M
 #myisam_sort_buffer_size = 1M
 #myisam_max_sort_file_size = 10G
 #myisam_max_extra_sort_file_size = 10G
 #myisam_repair_threads = 1
 #myisam_recover

lower_case_table_names = 1
 skip-name-resolve
 slave-skip-errors = 1032,1062
 replicate-ignore-db=mysql

server-id = 1

innodb_additional_mem_pool_size = 4M
 innodb_buffer_pool_size = 1G
 innodb_data_file_path = ibdata1:128M:autoextend
 innodb_file_io_threads = 4
 innodb_thread_concurrency = 8
 innodb_flush_log_at_trx_commit = 2
 innodb_log_buffer_size = 2M
 innodb_log_file_size = 4M
 innodb_log_files_in_group = 3
 innodb_max_dirty_pages_pct = 90
 innodb_lock_wait_timeout = 120
 innodb_file_per_table = 0

[mysqldump]
 quick
 max_allowed_packet = 2M

[mysqld_safe]
 log-error=/data/mysql_152.err
 pid-file=/data/mysqld.pid

从:my.cnf   关闭log-bin ,server-id 不能一致

[client]
 port        = 3306
 socket        = /application/mysql/tmp/mysql.sock

[mysql]
 no-auto-rehash

[mysqld]
 port        = 3306
 socket        = /application/mysql/tmp/mysql.sock
 basedir     = /application/mysql
 datadir        = /application/mysql/data
 open_files_limit    = 1024
 back_log = 600
 max_connections = 800
 max_connect_errors = 3000
 table_cache = 1024
 external-locking = FALSE
 max_allowed_packet =16M
 sort_buffer_size = 1M
 join_buffer_size = 1M
 thread_cache_size = 100
 thread_concurrency = 8
 query_cache_size = 2M
 query_cache_limit = 1M
 query_cache_min_res_unit = 2k
 #default_table_type = InnoDB
 thread_stack = 192K
 #transaction_isolation = READ-COMMITTED
 tmp_table_size = 2M
 max_heap_table_size = 2M
 long_query_time = 1
 #log_long_format#log-error = /application/mysql/data/error.log
 #log-slow-queries = /application/mysql/data/slow.log
 pid-file = /application/mysql/data/mysql.pid
 #log-bin = /application/mysql/data/mysql-bin
 relay-log = /application/mysql/data/relay-bin
 relay-log-info-file = /application/mysql/data/relay-log.info
 binlog_cache_size = 1M
 max_binlog_cache_size = 1M
 max_binlog_size = 2M
 expire_logs_days = 7
 key_buffer_size = 16M
 read_buffer_size = 1M
 read_rnd_buffer_size = 1M
 bulk_insert_buffer_size = 1M
 #myisam_sort_buffer_size = 1M
 #myisam_max_sort_file_size = 10G
 #myisam_max_extra_sort_file_size = 10G
 #myisam_repair_threads = 1
 #myisam_recover

lower_case_table_names = 1
 skip-name-resolve
 slave-skip-errors = 1032,1062
 replicate-ignore-db=mysql

server-id = 2

innodb_additional_mem_pool_size = 4M
 innodb_buffer_pool_size = 1G
 innodb_data_file_path = ibdata1:128M:autoextend
 innodb_file_io_threads = 4
 innodb_thread_concurrency = 8
 innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 2M
 innodb_log_file_size = 4M
 innodb_log_files_in_group = 3
 innodb_max_dirty_pages_pct = 90
 innodb_lock_wait_timeout = 120
 innodb_file_per_table = 0

[mysqldump]
 quick
 max_allowed_packet = 2M

[mysqld_safe]
 log-error=/application/mysql/data/mysql_153.err
 pid-file=/application/mysql/data/mysqld.pid

主库操作:
建立用于从库复制的账号sqlrep

grant replication slave on *.* to 'hncgo'@'192.168.200.%' identified by 'Hncgo110';
 flush privileges;
 show master status;

从库执行
mysql自动批量制作主从同步的语句

CHANGE MASTER TO
 MASTER_HOST='192.168.200.39',
 MASTER_PORT=3306,
 MASTER_USER='hncread',
 MASTER_PASSWORD='Hncgo110',
 MASTER_LOG_FILE='mysql-bin.000005',
 MASTER_LOG_POS=702;

开启同步

start slave;

检查是否成功

show slave status\G

创建只读用户：

GRANT SELECT ON *.* TO 'read'@'192.168.200.%' IDENTIFIED BY 'password123';

创建所有权限用户：

GRANT ALL ON *.* TO 'chenyong'@'localhost' IDENTIFIED BY 'password123';

FLUSH PRIVILEGES;