Mysql数据库安装及生产环境下主从库同步配置目录1安装M YSQL数据库......................................................................................................................................2生产环境下M Y SQL数据库主从同步配置.................................................................................................2.1 主数据库配置 (5)2.2 从数据库配置 (5)3监控服务器...............................................................................................................................................3.1 监控主数据库服务器 (6)3.2 监控从数据库服务器 (6)1安装Mysql数据库安装环境：系统：CentOS-6.6-x86_64数据库：MySQL-server-5.5.42-1.el6.x86_64；MySQL-client-5.5.42-1.el6.x86_641.SSH方式登录到MySQL服务器2.创建存放安装文件的目录[root@localhost /]# mkdir -p /sw/mysql553.上传安装文件到上一步创建的目录4.检查是否已安装过MySQL[root@localhost /]# rpm -qa | grep -i mysqlMySQL-client-5.5.42-1.el6.x86_64MySQL-server-5.5.42-1.el6.x86_645.如果已安装则移除，否则请跳过此步[root@localhost /]# yum -y remove MySQL-server-5.5.42-1.el6.x86_64[root@localhost /]# yum -y remove MySQL-client-5.5.42-1.el6.x86_64删除老版本mysql的开发头文件和库rm -fr /usr/lib/mysqlrm -fr /usr/include/mysqlrm -fr /var/lib/mysqlrm -f /etc/f6.安装MySQL[root@localhost /]# cd /sw/mysql55/[root@localhost mysql55]# rpm -ivh MySQL-server-5.5.42-1.el6.x86_64.rpmPreparing... ########################################### [100%] 1:MySQL-client ########################################### [100%] [root@localhost mysql55]# rpm -ivh MySQL-client-5.5.42-1.el6.x86_64.rpmPreparing... ########################################### [100%] 1:MySQL-server ########################################### [100%]7.配置MySQL[root@localhost mysql55]# cp /usr/share/mysql/f /etc/f[root@localhost mysql55]# vi /etc/f[client]#password = your_passwordport = 8819socket = /var/lib/mysql/mysql.sockdefault-character-set=utf8[mysqld]port = 8819socket = /var/lib/mysql/mysql.socklower_case_table_names=1skip-external-lockingkey_buffer_size = 384Mmax_allowed_packet = 8Mtable_open_cache = 512sort_buffer_size = 2Mread_buffer_size = 2Mread_rnd_buffer_size = 8Mmyisam_sort_buffer_size = 64Mthread_cache_size = 8Mquery_cache_size = 32M# Try number of CPU's*2 for thread_concurrencythread_concurrency = 16max_connections=10000character-set-server=utf8wait_timeout=31536000interactive_timeout=31536000user = mysqldefault_storage_engine = InnoDBpid-file = /var/lib/mysql/mysql.pid datadir = /var/lib/mysql/log_bin = /var/lib/mysql/mysql-binlog_error = /var/lib/mysql/mysql-error.log log_queries_not_using_indexes = 1#secondlong_query_time = 10slow_query_log = 1slow_query_log_file = /var/lib/mysql/mysql-slow.log server-id = 1binlog_format=mixed[mysqldump]quickmax_allowed_packet = 16M[mysql]no-auto-rehashdefault-character-set=utf8[myisamchk]key_buffer_size = 256Msort_buffer_size = 256Mread_buffer = 2Mwrite_buffer = 2M[mysqlhotcopy]interactive-timeout启动MySQL[root@localhost mysql55]# service mysql start8.初始化MySQL root用户密码，比如设置密码为：123456[root@localhost mysql]# mysql_secure_installation9.登录mysql后台管理程序[root@localhost mysql]# mysql -u root -p123456mysql>10.添加新账号mysql> grant all on *.* to 'dtoa'@'%' identified by 'dtoa123456';Query OK, 0 rows affected (0.00 sec)mysql> flush privileges;Query OK, 0 rows affected (0.00 sec)11.设置为开机自动启动[root@localhost mysql]# chkconfig mysql on2生产环境下MySQL数据库主从同步配置2.1 主数据库配置2.1.1.检查mysql的配置文件：# vi /etc/f在[mysqld]这个Section确认以下配置log_bin = /var/lib/mysql/mysql-bin#配置在数据库有变动时写二进制日志server-id = 1 #标识，唯一，值范围在：1至2^23-1 binlog-do-db = dtoa # 要给从机同步的库保存退出。

重启mysql 服务。

# Service mysql restart2.1.2. 进入mysql配置：登录mysql后台管理程序# mysql -u root -p123456添加新账号mysql> grant all on *.* to 'dtoa'@'%' identified by 'dtoa123456';锁主库表：mysql>flush tables with read lock;显示主库信息，记录file和position，从库设置会用到。

mysql>show masterstatus;+------------------+----------+--------------+------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |+------------------+----------+--------------+------------------+| mysql-bin.000004 | 106 | | |+------------------+----------+--------------+------------------+退出mysqlmysql>quit;2.1.3. linuxShell中对数据库的操作备份主机上的dtoa数据库：#mysqldump --master-data –u root –p dtoa > dtoa.sql把备份文件传到从机#rsync -vzrtopg dtoa.sql root@从机ip:/sw/进入mysql，解锁主库表# mysql -u root -p123456mysql>unlock tables;注：配置文件还可以配置的参数可根据情况选择设置：[mysqld]binlog-do-db = db2 # 要给从机同步的库binlog-ignore-db = mysql # 不给从机同步的库expire_logs_days = 2 # 自动清理2 天前的log文件，可根据需要修改2.2 从数据库配置检查mysql的配置文件：# vi /etc/f在[mysqld]这个Section确认以下配置log_bin = /var/lib/mysql/mysql-bin#配置在数据库有变动时写二进制日志server-id = 2 #标识，唯一，值范围在：1至2^23-1replicate-do-db =dtoa #如果不设置就是要同步所有数据库保存退出。