linux下安装mysql和mysql主从配置

参考:
http://blog.csdn.net/geek87/article/details/10090637
http://www.cnblogs.com/wanghetao/p/3806888.html

涉及到的问题:
wget, apt-get和yum的区别.
wget不是安装方式, 他是一种下载软件的方式, 类似与迅雷, 如果要下载一个软件, 我们可以直接wget 下载地址.
ap-get是ubuntu下的一个软件安装方式, 它是基于debain.
yum是redhat, centos下的一个软件安装方式, 它是基于Linux的.

1. 安装Mysql.
yum install mysql
yum install mysql-server (ubuntu: apt-get install mysql-server)
yum install mysql-devel
安装完mysql, 设置mysql目录的拥有者和所属的用户组 
chgrp -R mysql /var/lib/mysql
chmod -R 770 /var/lib/mysql

查看mysql状态:
root@stress5947:~# service mysql status
mysql start/running, process 14024
root@stress5947:~# ps -ef|grep mysql
mysql    14024     1  0 10:47 ?        00:00:00 /usr/sbin/mysqld
root     14592 14354  0 10:53 pts/1    00:00:00 grep --color=auto mysql
root@stress5947:~# 

启动mysql:  service mysql start (service mysqld start)
停止mysql:  service mysql stop (service mysqld stop)
连接mysql:  mysql
为root用户设置密码: SET PASSWORD FOR 'root'@'localhost' = PASSWORD('123456');
为root用户设置完密码, 就不能通过mysql命令连接mysql了, 需要通过mysql -uroot -p123456指令连接.

补充: ubuntu环境下安装mysql时，即执行 apt-get install mysql-server指令时，会提示输入root密码，
所以, 连接MySQL时，需要直接使用mysql -uroot -proot链接。


2.创建允许其他机器连接的用户.
在mysql.user表中, Host如果配置为localhost, 是指该用户只能在本地登录,不能在另外一台机器上登录.
如果想远程登录的话, 将Host配置为"%", 表示允许在任何一台机器上都可以登录,  同时也可以指定某台机器可以登录.

创建一个用户, 允许在其他机器上登录.
mysql> insert into mysql.user(Host,User,Password) values("%","ilucky",password("123456"));
Query OK, 1 row affected (0.00 sec)

刷新权限,
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec) 
然后, 通过ilucky用户登录, 登录成功, 同时在其他主机上通过mysql客户端(navicate)登录也没有问题.

补充: 如果连接有问题，在navicate客户端报10038错误，需要将mysql的配置文件my.conf中的
bind-address        = 127.0.0.1 修改为bind-address     = 0.0.0.0
参考: http://blog.csdn.net/chana1101/article/details/39641415
如果navicate连接MySQL报异常: 1045-access denied for user 'root'@......解决方案如下:
UPDATE user SET Password=PASSWORD('root') where USER='root';
flush privileges;
参考: http://www.cnblogs.com/qq78292959/p/3641972.html

root@stress5947:~# vi /etc/mysql/my.cnf
然后，重启mysql即可。
root@stress5947:~# service mysql restart
mysql stop/waiting
mysql start/running, process 16048
root@stress5947:~# 

但是紧接着发现一个问题，当连接mysql后，创建一个数据库， 但是通过navicate连接后没有发现这个数据库。
mysql> create database test;
Query OK, 1 row affected (0.00 sec)

怎么回事呢？原来是ilucky这个用户的权限问题, 可以通过mysql> select * from user;查看所有用户的权限。
通过如下指令为用户赋予所有权限.
mysql> GRANT all privileges ON *.* TO 'ilucky'@'%';

刷新权限.
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
此时再通过navicate连接(重新连接)，查看(创建)数据库，没有问题了。

3. MySQL主从配置.
参考: https://yq.aliyun.com/articles/46157
在主库新建专门用于同步的数据库账号root
mysql> GRANT REPLICATION SLAVE ON *.* TO 'root'@'%' IDENTIFIED BY 'root';
Query OK, 0 rows affected (0.00 sec)

mysql> 
修改主mysql的配置文件，添加如下配置:
root@stress5947:/etc/mysql# cat my.cnf
[mysqld]
server-id=1
replicate-wild-ignore-table = mysql.%
replicate-wild-ignore-table = test.%
log-bin = mysql-bin  
log-bin-index = mysql-bin.index  
relay-log = mysql-relay 
relay-log-index = relay-log.index 
replicate-wild-ignore-tabl 意思是同步过程中需要忽略的表。
 # 二进制日志，强制开启
 # 记录二进制日志索引文件
 # 中继日志
 # 记录中继日志索引文件

修改从mysql的配置文件，添加如下配置:
[mysqld]
server-id=2
read-only 
skip-slave-start 
relay-log = mysql-relay 
relay-log-index = relay-log.index
log-bin = mysql-bin
log-bin-index = mysql-bin.index
replicate-wild-ignore-table = mysql.%
replicate-wild-ignore-table = test.%

然后，再从mysql的后台执行如下指令启动从库:
mysql> change master to master_host='10.0.3.47',master_port=3306,master_user='root',master_password='root',master_log_file='mysql-bin.000006',master_log_pos=465;
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
可以通过如下指令验证是否配置成功:
mysql>  show slave status;
+----------------------+-------------+-------------+-------------+---------------+------------------+---------------------+--------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+-----------------------------------------------------------------------------------+----------------+----------------+-----------------------------+------------------+
| Slave_IO_State       | Master_Host | Master_User | Master_Port | Connect_Retry | Master_Log_File  | Read_Master_Log_Pos | Relay_Log_File     | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master | Master_SSL_Verify_Server_Cert | Last_IO_Errno | Last_IO_Error                                                                     | Last_SQL_Errno | Last_SQL_Error | Replicate_Ignore_Server_Ids | Master_Server_Id |
+----------------------+-------------+-------------+-------------+---------------+------------------+---------------------+--------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+-----------------------------------------------------------------------------------+----------------+----------------+-----------------------------+------------------+
| Connecting to master | 10.0.3.47   | root        |        3306 |            60 | mysql-bin.000006 |                 465 | mysql-relay.000001 |             4 | mysql-bin.000006      | Connecting       | Yes               |                 |                     |                    |                        |                         | mysql.%,test.%              |          0 |            |            0 |                 465 |             107 | None            |                |             0 | No                 |                    |                    |                 |                   |                |                  NULL | No                            |          2003 | error connecting to master 'root@10.0.3.47:3306' - retry-time: 60  retries: 86400 |              0 |                |                             |                0 |
+----------------------+-------------+-------------+-------------+---------------+------------------+---------------------+--------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+-----------------------------------------------------------------------------------+----------------+----------------+-----------------------------+------------------+
1 row in set (0.00 sec)

mysql> 
如果报如上错误， 首先在其他机器上通过telnet命令，telnet不上。
解决方案: 需要将mysql的配置文件my.conf中的
bind-address        = 127.0.0.1 修改为bind-address     = 0.0.0.0
参考: http://blog.csdn.net/chana1101/article/details/39641415

重启slave:
mysql> stop slave;
Query OK, 0 rows affected (0.09 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql>  show slave status;
+----------------+-------------+-------------+-------------+---------------+------------------+---------------------+--------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+----------------------------------------------------------------------------------------------+----------------+----------------+-----------------------------+------------------+
| Slave_IO_State | Master_Host | Master_User | Master_Port | Connect_Retry | Master_Log_File  | Read_Master_Log_Pos | Relay_Log_File     | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master | Master_SSL_Verify_Server_Cert | Last_IO_Errno | Last_IO_Error                                                                                | Last_SQL_Errno | Last_SQL_Error | Replicate_Ignore_Server_Ids | Master_Server_Id |
+----------------+-------------+-------------+-------------+---------------+------------------+---------------------+--------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+----------------------------------------------------------------------------------------------+----------------+----------------+-----------------------------+------------------+
|                | 10.0.3.47   | root        |        3306 |            60 | mysql-bin.000006 |                 465 | mysql-relay.000001 |             4 | mysql-bin.000006      | No               | Yes               |                 |                     |                    |                        |                         | mysql.%,test.%              |          0 |            |            0 |                 465 |             107 | None            |                |             0 | No                 |                    |                    |                 |                   |                |                  NULL | No                            |          1236 | Got fatal error 1236 from master when reading data from binary log: 'Binary log is not open' |              0 |                |                             |                1 |
+----------------+-------------+-------------+-------------+---------------+------------------+---------------------+--------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+----------------------------------------------------------------------------------------------+----------------+----------------+-----------------------------+------------------+
1 row in set (0.00 sec)

mysql> 
如果报如上错误: 可能是配置不够全，可以在主mysql上执行show master slave。
如果继续重启slave，报如下错误, 意思是之前做过slave，所以需要reset一下:
ERROR 1201 (HY000): Could not initialize master info structure; more error messages can be found in the MySQL error log

mysql> reset slave;
Query OK, 0 rows affected (0.00 sec)

mysql> start slave;
Query OK, 0 rows affected (0.55 sec)

mysql> 

mysql> show slave status;
+----------------------------------+-------------+-------------+-------------+---------------+------------------+---------------------+--------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+
| Slave_IO_State                   | Master_Host | Master_User | Master_Port | Connect_Retry | Master_Log_File  | Read_Master_Log_Pos | Relay_Log_File     | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master | Master_SSL_Verify_Server_Cert | Last_IO_Errno | Last_IO_Error | Last_SQL_Errno | Last_SQL_Error | Replicate_Ignore_Server_Ids | Master_Server_Id |
+----------------------------------+-------------+-------------+-------------+---------------+------------------+---------------------+--------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+
| Waiting for master to send event | 10.0.3.47   | root        |        3306 |            60 | mysql-bin.000001 |                 107 | mysql-relay.000010 |           253 | mysql-bin.000001      | Yes              | Yes               |                 |                     |                    |                        |                         | mysql.%,test.%              |          0 |            |            0 |                 107 |             405 | None            |                |             0 | No                 |                    |                    |                 |                   |                |                     0 | No                            |             0 |               |              0 |                |                             |                1 |
+----------------------------------+-------------+-------------+-------------+---------------+------------------+---------------------+--------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+
1 row in set (0.01 sec)   

如果显示以下2个YES，则为配置成功：
Slave_IO_Running: Yes   #负责从库去主库读取BINLOG日志，并写入从库的中继日志中
Slave_SQL_Running: Yes  #负责读取并执行中继日志中的BINGLOG,转换SQL语句后应用到数据库汇总

mysql的主从配置完成后，进行如下测试:
在master上执行:
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| aplay              |
| masterslave        |
| masterslave2       |
| mysql              |
| performance_schema |
| test               |
+--------------------+
7 rows in set (0.00 sec)

mysql> create database ms;
Query OK, 1 row affected (0.00 sec)

mysql> 
在slave上执行:
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| ms                 |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.01 sec)

mysql> 
补充: mysql的主从配置完成，并生效后，并不是同步主mysql的所有数据，而是他们保存着一个pos(位移量)，即从pos开始同步。
在java中如何使用主从的mysq呢? 注意: 客户端一定要都支持telent主从，否则会有问题。

4. MySQL主从原理
user=root
password=root
url=jdbc:mysql:replication://10.0.3.47:3306,10.0.3.49:3306/ms?useUnicode=true&characterEncoding=utf8
配置如上: 结合如下demo

另外，在做javaagent适配的时候发现了如下问题:
// IluckySi@20170419: 针对mysql主从做适配.
//2017-04-19 12:17:59 INFO  MysqlFormatProcessor connectionValue---->com.mysql.jdbc.JDBC4Connection@38151ff5
//2017-04-19 12:17:46 INFO  MysqlFormatProcessor connectionValue---->com.mysql.jdbc.JDBC4LoadBalancedMySQLConnection@378f5049
 agent.logInfo("MysqlFormatProcessor connectionValue---->"+ connectionValue);
 if(connectionValue.toString().contains("LoadBalanced")) {
        Method getJDBC4ConnectionMethod = Util.findMethod(connectionValue.getClass(),"getJDBC4Connection", null);
        if(getJDBC4ConnectionMethod!=null) {
             getJDBC4ConnectionMethod.setAccessible(true);
              connectionValue = getJDBC4ConnectionMethod.invoke(connectionValue);
       }
}
注意: 在不做主从配置的情况，即通过普通方式连接mysql，url中是什么，对应的Connection就是什么，即JDBC4Connection.
但是对于主从配置的情况，url中包含多个mysql配置，那么具体到某个操作时，对应的Connetcion是什么呢？
经过测试，当操作为Insert等操作时，对应的Connection和普通方式一样是JDBC4Connection，但是当操作为Select时，
对应的Connection变为了JDBCLoadBaancedMySQLConnection，这是为什么呢？
其实很简答，对于一些非select操作，为了保证数据原子性，一致性，只能对master操作，然后所有slave进行同步，然而url中只能包含一个
master，所以当发现操作为非select时，对应的connection为JDBCLoadBaancedMySQLConnection。
然而当操作为select时，可以对所有slave进行查询，那么到底从哪个slave查询呢？这涉及到一个策略的问题。首先看下面代码:


当操作为select时，JDBCLoadBaancedMySQLConnection会将任务交给一个代理(proxy)，什么任务呢？即从哪个slave查询数据。
这个代理的主要任务是: 根据策略(三种)，选择一个合适的JDBC4Connection。