享知行·技术:Linux环境Mysql安装和踩过的那些坑

Linux环境mysql安装，主从复制、跨机房数据同步、备份还原。

mysql下载安装地址：https://dev.mysql.com/downloads/mysql/5.6.html

Mysql安装：

• 解压文件：tar -zxvf mysql-5.6.42-linux-glibc2.12-x86_64.tar-1.gz增加用户组：groupadd mysql用户组增加用户：useradd -r -g mysql mysql修改数据库文件夹名称：mv mysql-5.6.42-linux-glibc2.12-x86_64 mysql将数据库文件夹移动到/usr/local目录：mv mysql /usr/localcd /usr/local/mysqlmkdir logtouch /usr/local/mysql/log/mysql.errchown -R mysql:mysql ./创建文件my.cnf，并拷贝到/etc目录下chown -R mysql:mysql /tmp 采坑1：安装过程中失败因为缺少软件，安装mysql需要的软件yum install libaio-devel.x86_64yum install numactl.x86_64yum install perl-Data-Dumper.x86_64初始化数据库./scripts/mysql_install_db --user=mysql --defaults-file=/etc/my.cnf 建立软链ln -s /usr/local/mysql/bin/mysql /usr/bin拷贝之后才可以通过service mysqld restart 重启mysqlcp support-files/mysql.server /etc/rc.d/init.d/mysqld修改密码： ./bin/mysqladmin -u root password 'root,123' 如果不能修改成功，通过下面的方式启动来修改密码./bin/mysqld_safe --user=mysql --skip-grant-tables --skip-networking &mysql -u mysql mysql -P 3556mysql > use mysql;mysql > UPDATE user SET Password=PASSWORD('root,123') where USER='root';mysql > FLUSH PRIVILEGES;mysql > quit; 重启数据库：service mysqld restart #给客户端授权（通过执行下面命令，可以通过本地远程连接数据库）采坑2：数据库安装之后需要给客户端授权，客户端才可以远程连接数据库GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'root,123' WITH GRANT OPTION; #关闭主数据库防火墙 如果连不上关闭防火墙systemctl stop firewalld.service

数据库常规配置：my.cnf

• [client]port = 3556socket = /tmp/mysql.sock[mysqld]port = 3556socket = /tmp/mysql.sockskip-external-lockingdefault-storage-engine=INNODBkey_buffer_size = 384Mmax_allowed_packet = 1Mtable_open_cache = 512sort_buffer_size = 2Mread_buffer_size = 2Mread_rnd_buffer_size = 8Mmyisam_sort_buffer_size = 64Mthread_cache_size = 8query_cache_size = 32M# Try number of CPU's*2 for thread_concurrencythread_concurrency = 8skip-name-resolveskip-symbolic-linkslog-slave-updates = 1#skip-networkingmax_allowed_packet = 256Mtmp_table_size=128Mmax_heap_table_size=128Msort_buffer_size = 16Mquery_cache_type = 0query_cache_size = 256Mquery_cache_limit = 2Mlower_case_table_names = 1max_connections=1000#about myisamkey_buffer_size=128Mlog-error = /usr/local/mysql/log/mysql.errlong-query-time = 0.5slow-query-log = 1slow-query-log-file = /usr/local/mysql/log/mysql-slow.loggeneral-log=0general-log-file = /tmp/mysql.logserver-id = 4log-bin = mysql-binmax_binlog_size = 1000Mbinlog_format = ROWexpire_logs_days = 10sync_binlog = 0#skip-slave-start#relay-log=mysql-relay#relay-log-info-file=mysql-relay.infoinnodb_data_home_dir = /usr/local/mysql/datainnodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextendinnodb_log_group_home_dir = /usr/local/mysql/datainnodb_buffer_pool_size = 384Minnodb_file_io_threads = 4innodb_thread_concurrency = 16innodb_additional_mem_pool_size = 20Minnodb_log_file_size = 512Minnodb_log_buffer_size = 8Minnodb_flush_log_at_trx_commit = 1innodb_lock_wait_timeout = 50[mysqldump]quickmax_allowed_packet = 16M[mysql]no-auto-rehash#safe-updates[myisamchk]key_buffer_size = 256Msort_buffer_size = 256Mread_buffer = 2Mwrite_buffer = 2M[mysqlhotcopy]interactive-timeout

Mysql主从复制

• 重复Mysql安装的过程，把新安装数据库作为从库采坑3：my.cnf中的server-id = 4 这个参数需要和主库配置的值不一样

• 配置主从：mysql > CHANGE MASTER TO MASTER_HOST='主数据库的ip地址',MASTER_PORT=3556,MASTER_USER='root',MASTER_PASSWORD='root,123',MASTER_LOG_FILE='上图的File',MASTER_LOG_POS=上图的position;mysql > start slave;mysql > show slave status\G;

• #都是Yes才算成功 如上图Slave_IO_Running: YesSlave_SQL_Running: Yes采坑4：主从同步会发生错误，导致数据不同步，例如主数据库删除一条数据，从库这条不存在，主从发生错误，会停止同步解决方法：my.cnf增加配置skip-external-locking
• 数据库备份还原./mysqldump -h 数据库ip -P 3556 -u root -p cobra > /usr/local/cobra.dump全量还原：mysql -uroot -P 3556 -pmysql > use cobra;mysql > source /usr/local/cobra.dump mysql定时备份python脚本：每日3点更新 # -*- coding:utf-8 -*-import subprocessimport scheduleimport time class MysqlBackup(object): ''' mysql_backup and save api for thi class ''' def __init__(self, *arg): pass def runback(self): print("mysql backup start .......") status = subprocess.getstatusoutput("rm -rf /home/mysql_back/*") back_status = subprocess.call("/usr/local/mysql/bin/mysqldump -P 3556 cobra > /home/mysql_back/cobra.dump", shell=True) print("mysql backup end..........") def backMysql(self): try: self.runback() except Exception as e: print(e) def runserver(self): self.backMysql() mysqlBackup = MysqlBackup()schedule.every().day.at("03:00").do(mysqlBackup.runserver)print("mysql begin -----------")while True: schedule.run_pending() time.sleep(10) print("sleep 10 second -----------")
• 跨机房数据同步：使用阿里开源工具otterotter安装请参考：https://github.com/alibaba/otter/wiki/QuickStart安装node和manage

第一步配置数据库数据源：

配置表：

配置canel：

配置channel：

配置pipeline:

配置映射关系：

最后运行channel，就可以完成增量同步。