知识扩展：在centos6.7下使用yum安装数据库之后需要改变MySQL数据库的默认编码
查看数据库默认编码 mysql> status; -------------- mysql Ver 14.14 Distrib 5.1.73, for redhat-linux-gnu (x86_64) using readline 5.1 Connection id: 2 Current database: abaotest Current user: root@localhost SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 5.1.73 Source distribution Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: latin1 Db characterset: utf8 Client characterset: latin1 Conn. characterset: latin1 UNIX socket: /var/lib/mysql/mysql.sock Uptime: 6 min 33 sec Threads: 1 Questions: 61 Slow queries: 0 Opens: 22 Flush tables: 1 Open tables: 9 Queries per second avg: 0.155 安装MySQL数据库时的默认编码是latin1，实际使用时可能要使用其他编码。下文描述如何将默认编码改为utf8： 首先修改MySQL的配置文件/etc/my.cnf： 在[client]下追加： default-character-set=utf8 在[mysqld]下追加： character-set-server=utf8 在[mysql]下追加： default-character-set=utf8 修改完毕后，使用如下命令之一重启mysql服务： service mysql restart /etc/init.d/mysql restart 改变后，之前手工创建的数据库如未显式指定编码，则其编码仍是默认的latin1，可使用如下命令更改编码： mysql> alter database db_name CHARACTER SET utf8; 2.3、停止主服务器 并 配置[root@centos67-68 ~]# service mysqld stop
停止 mysqld： [确定]
修改my.cnf文件并在[mysqld]选项中加入以下内容：
[root@centos67-68 ~]# vi /etc/my.cnf
[mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql # Disabling symbolic-links is recommended to prevent assorted security risks character-set-server=utf8 symbolic-links=0 #---------以下是在原配置文件中添加的内容--------- #启用二进制日志，默认存在/var/lib/mysql 下面 log-bin=mysqllog #本机数据库ID 标示。其中master_id必须为1到232之间的一个正整数值 server-id=1 #可以被从��务器复制的库。二进制需要同步的数据库名abaotest binlog-do-db=abaotest #不可以被从服务器复制的库mysql # binlog-ignore-db=mysql #---------以上是在原配置文件中添加的内容--------- [mysql] default-character-set=utf8 [client] default-character-set=utf8 [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid 2.4、重启服务[root@centos67-68 ~]# service mysqld restart
停止 mysqld： [确定]
正在启动 mysqld： [确定]
2.5、授权从服务器abao67可以使用账号slave连接到主服务器abao68 mysql> grant replication slave on *.* to slave@192.168.1.67 identified by "123456"; Query OK, 0 rows affected (0.00 sec) 2.6、查看服务器状态信息和二进制日志 [root@centos67-68 ~]# ls /var/lib/mysql/ abaotest ibdata1 ib_logfile0 ib_logfile1 mysql mysqllog.000001 mysqllog.index mysql.sock test [root@centos67-68 ~]# mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.1.73-log Source distribution Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show master status; +-----------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +-----------------+----------+--------------+------------------+ | mysqllog.000001 | 256 | abaotest | | +-----------------+----------+--------------+------------------+ 1 row in set (0.00 sec) mysql> show binlog events \G *************************** 1. row *************************** Log_name: mysqllog.000001 Pos: 4 Event_type: Format_desc Server_id: 1 End_log_pos: 106 Info: Server ver: 5.1.73-log, Binlog ver: 4 *************************** 2. row *************************** Log_name: mysqllog.000001 Pos: 106 Event_type: Query Server_id: 1 End_log_pos: 256 Info: grant replication slave on *.* to slave@192.168.1.67 identified by "123456" 2 rows in set (0.00 sec) 注意：这里记住File的值： mysqllog.000001和Position的值：256，后面会用到。 2.7、为了保证主从数据库的一致性，可以使用如下方法： [root@centos67-68 ~]# mysqldump -uroot -p -A >all.sql Enter password: mysqldump: Got error: 1045: Access denied for user 'root'@'localhost' (using password: YES) when trying to connect [root@centos67-68 ~]# mysqldump -uroot -p -A >all.sql Enter password: -- Warning: Skipping the data of table mysql.event. Specify the --events option explicitly. [root@centos67-68 ~]# ls all.sql anaconda-ks.cfg book_utf8.sql install.log install.log.syslog [root@centos67-68 ~]# rm -rf all.sql [root@centos67-68 ~]# mysqldump -uroot -p -A >all.sql Enter password: -- Warning: Skipping the data of table mysql.event. Specify the --events option explicitly. [root@centos67-68 ~]# scp all.sql 192.168.1.67:/root The authenticity of host '192.168.1.67 (192.168.1.67)' can't be established. RSA key fingerprint is c8:62:61:31:24:fa:77:44:da:be:26:d8:85:80:95:3a. Are you sure you want to continue connecting (yes/no)? yest^H Warning: Permanently added '192.168.1.67' (RSA) to the list of known hosts. root@192.168.1.67's password: all.sql 100% 521KB 521.3KB/s 00:00 3、配置从服务器abao67 3.1、测试是否可以正常连接到主服务器 [root@abao67 ~]# mysql -u slave -h192.168.1.68 -p123456 Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 7 Server version: 5.1.73-log Source distribution Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | test | +--------------------+ 2 rows in set (0.01 sec)