MySQL多实例配置问题

石岩 发布于 2015/07/23 09:05
阅读 1K+
收藏 0

如下是MySQL-5.6.25在CentOS7下的源码编译后的多实例配置步骤,但一直报一个错误,找一天没找到原因,请求大神出现指导。具体报错:ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)。报错日志会在配置最后。

在本文中,通过在mysql上开启两个端口(3306和3307来配置多实例,因为下面的主从同步要使用这两个端口来模拟)  

           1)创建目录(log目录是存放mysql日志的地方)
              mkdir -p /data/{3306,3307}/data
              mkdir -p /data/{3306,3307}/log
           2)在/data/3306中新建my.cnf
               cd /data/3306
               vi my.cnf
              把如下内容拷贝到该文件中:
                [client]
                port = 3306
                socket = /data/3306/mysql.sock
                
                [mysqld]
                port=3306
                socket = /data/3306/mysql.sock
                pid-file = /data/3306/data/mysql.pid
                basedir = /usr/local/mysql
                datadir = /data/3306/data
                server-id=1
                #log-bin=mysql-bin
                #log-bin-index= mysql-bin.index
                
                # LOGGING
                log_error=/data/3306/log/mysql-error.log
                slow_query_log_file=/data/3306/log/mysql-slow.log
                slow_query_log=1
             同样地,在/data/3307中新建my.cnf
  cd /data/3307
  vi my.cnf
把如下内容拷贝到该文件中(把上面的3306改为3307,还有server-id的值)
 [client]
               port = 3307
               socket = /data/3307/mysql.sock
               
               [mysqld]
               port=3307
               socket = /data/3307/mysql.sock
               pid-file = /data/3307/data/mysql.pid
               basedir = /usr/local/mysql
               datadir = /data/3307/data
               server-id=3
               #log-bin=mysql-bin
               #log-bin-index= mysql-bin.index
               
               # LOGGING
               log_error=/data/3307/log/mysql-error.log
               slow_query_log_file=/data/3307/log/mysql-slow.log
               slow_query_log=1
检查一下目录结构,看看有没有把文件放错地方
               tree /data
                /data
                 ├── 3306
                 │ ├── data
                 │ ├── log
                 │ └── my.cnf
                 
                 └── 3307
                 │ ├── data
                 │ ├── log
                 │ └── my.cnf   
          3)创建启动文件
             在/data/3306中新建mysql启动文件
 cd /data/3306
              vi mysql
             把如下内容拷贝到该文件中
                #!/bin/sh
                port=3306
                mysql_user="root"
                mysql_pwd=""
                CmdPath="/usr/local/mysql/bin"
                
                #startup function
                function_start_mysql()
                {
                  printf "Starting MySQL...\n"
                  /bin/sh ${CmdPath}/mysqld_safe --defaults-file=/data/${port}/my.cnf 2>&1 > /dev/null &
                }
                
                #stop function
                function_stop_mysql()
                {
                  printf "Stoping MySQL...\n"
                  ${CmdPath}/mysqladmin -u ${mysql_user} -p${mysql_pwd} -S /data/${port}/mysql.sock shutdown
                }
                
                #restart function
                function_restart_mysql()
                {
                  printf "Restarting MySQL...\n"
                  function_stop_mysql
                  sleep 2
                  function_start_mysql
                }
                
                case $1 in
                start)
                function_start_mysql
                ;;
                
                stop)
                function_stop_mysql
                ;;
                
                restart)
                function_restart_mysql
                ;;
                *)
                
                printf "Usage: /data/${port}/mysql {start|stop|restart}\n"
                esac
             同理,在/data/3307中新建mysql启动文件
               cd /data/3307
               vi mysql
             把如下内容拷贝到该文件中:
                #!/bin/bash


                port=3307
                mysql_user="root"
                mysql_pwd=""
                CmdPath="/usr/local/mysql/bin"
                
                #startup function
                function_start_mysql()
                {
                 printf "Starting MySQL...\n"
                 /bin/sh ${CmdPath}/mysqld_safe --defaults-file=/data/${port}/my.cnf 2>&1 > /dev/null &
                }
                
                #stop function
                function_stop_mysql()
                {
                  printf "Stoping MySQL...\n"
                  ${CmdPath}/mysqladmin -u ${mysql_user} -p${mysql_pwd} -S /data/${port}/mysql.sock shutdown
                }
                
                #restart function
                function_restart_mysql()
                {
                  printf "Restarting MySQL...\n"
                  function_stop_mysql
                  sleep 2
                  function_start_mysql
                }
                
                case $1 in
                start)
                function_start_mysql
                ;;
                stop)
                function_stop_mysql
                ;;
                restart)
                function_restart_mysql
                ;;
                *)
                printf "Usage: /data/${port}/mysql {start|stop|restart}\n"
                esac
          4)修改文件拥有者和权限
               chown -R mysql:mysql /data
               find /data -name mysql -exec chmod 700 {} \;
          5)添加mysql启动路径 
                echo 'export PATH=$PATH:/usr/local/mysql/bin' >>/etc/profile
                source /etc/profile
                echo $PATH /usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin:/usr/local/mysql/bin
             查看/etc/profile文件中mysql路径已经添加
          6)初始化数据库 
               cd /usr/local/mysql/scripts
               ./mysql_install_db --defaults-file=/usr/local/mysql/data/3306/my.cnf --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data/3306/data &
               ./mysql_install_db --defaults-file=/usr/local/mysql/data/3307/my.cnf --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data/3307/data &   
 7)配置防火墙
     firewall-cmd --zone=public --add-port=3306/tcp --permanent
 firewall-cmd --zone=public --add-port=3307/tcp --permanent
 firewall-cmd --reload
 8)启动mysql
    分别启动两个端口
   /data/3306/mysql start
/data/3307/mysql start
netstat -lntp | grep 330    #查看是否启动进程
 9)登陆mysql
    刚安装完的mysql是没有登陆密码的
  mysql -S /data/3306/mysql.sock
如果不成功,检查/data/3306/log目录下的mysql-error.log日志,逐一排除错误,
如果登陆成功,下面就修改登录密码(不建议在shell环境下修改密码,否则别人只要查看命令历史就能看到密码(前提是你没有情况命令历史))
  update mysql.user set password=password("123456") where user='root';
  flush privileges;
同理,使用上面的方法修改3307的登陆密码
要把上面更改后的密码写回到mysql的启动文件中(否则每次启动、关闭、重启mysql都要输入密码)
              sed -i 's/mysql_pwd=\"\"/mysql_pwd=\"123456\"/g' /data/3306/mysql
              sed -i 's/mysql_pwd=\"\"/mysql_pwd=\"123456\"/g' /data/3307/mysql
          10)重启mysql
                /data/3306/mysql restart
                netstat -lntp | grep 330

              可以看到3306端口重启成功,同理可以重启3307端口。





报错日志内容如下:

150722 02:15:24 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data
2015-07-22 02:15:33 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for 
more details).
2015-07-22 02:15:33 0 [Note] /usr/local/mysql/bin/mysqld (mysqld 5.6.25) starting as process 10316 ...
2015-07-22 02:15:33 10316 [Note] Plugin 'FEDERATED' is disabled.
/usr/local/mysql/bin/mysqld: Table 'mysql.plugin' doesn't exist
2015-07-22 02:15:33 10316 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
2015-07-22 02:15:33 10316 [Note] InnoDB: Using atomics to ref count buffer pool pages
2015-07-22 02:15:33 10316 [Note] InnoDB: The InnoDB memory heap is disabled
2015-07-22 02:15:33 10316 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2015-07-22 02:15:33 10316 [Note] InnoDB: Memory barrier is not used
2015-07-22 02:15:33 10316 [Note] InnoDB: Compressed tables use zlib 1.2.3
2015-07-22 02:15:33 10316 [Note] InnoDB: Using Linux native AIO
2015-07-22 02:15:33 10316 [Note] InnoDB: Using CPU crc32 instructions
2015-07-22 02:15:33 10316 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2015-07-22 02:15:33 10316 [Note] InnoDB: Completed initialization of buffer pool
2015-07-22 02:15:34 10316 [Note] InnoDB: The first specified data file ./ibdata1 did not exist: a new database to be created!
2015-07-22 02:15:34 10316 [Note] InnoDB: Setting file ./ibdata1 size to 12 MB
2015-07-22 02:15:34 10316 [Note] InnoDB: Database physically writes the file full: wait...
2015-07-22 02:15:39 10316 [Note] InnoDB: Setting log file ./ib_logfile101 size to 48 MB
2015-07-22 02:15:44 10316 [Note] InnoDB: Setting log file ./ib_logfile1 size to 48 MB
2015-07-22 02:15:45 10316 [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
2015-07-22 02:15:45 10316 [Warning] InnoDB: New log files created, LSN=45781
2015-07-22 02:15:45 10316 [Note] InnoDB: Doublewrite buffer not found: creating new
2015-07-22 02:15:46 10316 [Note] InnoDB: Doublewrite buffer created
2015-07-22 02:15:46 10316 [Note] InnoDB: 128 rollback segment(s) are active.
2015-07-22 02:15:46 10316 [Warning] InnoDB: Creating foreign key constraint system tables.
2015-07-22 02:15:46 10316 [Note] InnoDB: Foreign key constraint system tables created
2015-07-22 02:15:46 10316 [Note] InnoDB: Creating tablespace and datafile system tables.
2015-07-22 02:15:46 10316 [Note] InnoDB: Tablespace and datafile system tables created.
2015-07-22 02:15:46 10316 [Note] InnoDB: Waiting for purge to start
2015-07-22 02:15:46 10316 [Note] InnoDB: 5.6.25 started; log sequence number 0
2015-07-22 02:15:46 10316 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID:
 3cb1ba28-3052-11e5-889e-000c29144a61.
2015-07-22 02:15:46 10316 [Note] Server hostname (bind-address): '*'; port: 3306
2015-07-22 02:15:46 10316 [Note] IPv6 is available.
2015-07-22 02:15:46 10316 [Note]   - '::' resolves to '::';
2015-07-22 02:15:46 10316 [Note] Server socket created on IP: '::'.
2015-07-22 02:15:46 10316 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.user' doesn't exist
150722 02:15:47 mysqld_safe mysqld from pid file /usr/local/mysql/data/bogon.pid ended



加载中
0
益达先生
益达先生
2015-07-22 02:15:33?哥们你起来好早啊!
0
超级大富
超级大富
看日志,是表不存在,是不是复制data 的时候出错了?
石岩
石岩
应该不是,到第七步都是成功的操作,在第八步启动数据库时提示没有mysql.sock文件,我在网上找半天出没有相类似的问题。在/tmp/及/var/lib/mysql/下都没有这个文件存在。
0
1721445902
1721445902
不知道耶
0
mlovewt
mlovewt
你要把原来data目录下的文件拷贝到data中,mysqld_multi 
石岩
石岩
所有文件及文件夹都拷贝到新的data目录下?用在重构吗?
返回顶部
顶部