ubuntu新环境的mysql搭建

叁歲伎倆 2022-05-15 03:59 223阅读 0赞

## Mysql单机搭建 ##

首先,安装mysql的server和client端 环境为ubuntu 16.04

sudo apt-get install mysql-server
    sudo apt-get install mysql-client

安装好之后,需要修改数据目录,因为,我们挂载的盘在/mnt 所以要将数据目录修改为/mnt/data/msyql/data中  
试了几遍直接改不成功,网搜 发现问题,需要修改 /etc/apparmor.d/usr.bin.mysqld 下的mysql权限

sudo vim /etc/apparmor.d/usr.bin.mysqld
    # Allow data dir access
      /mnt/data/mysql/data/ rwk,
      /mnt/data/mysql/data/** rwk,
    
    # Allow log file access
      /var/log/mysql.err rw,
      /var/log/mysql.log rw,
      /mnt/data/mysql/log/ r,
      /mnt/data/mysql/log/** rw,

然后修改mysql的真正配置数据和日志目录的文件 /etc/mysql/mysql.conf.d/mysqld.cnf

sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf 
    将其中的 datadir 和log_err修改为现在的log
    datadir         = /mnt/data/mysql/data
    log_error = /mnt/data/mysql/log/error.log

这样修改过后,mysql目录是没有经过初始化的,不能直接的使用,需要执行如下语句

sudo service mysql stop
    sudo chmod 700 /mnt/data/mysql/data
    sudo chown -R mysql:mysql /mnt/data/mysql/data
    sudo chmod 755 /mnt/data/mysql/log
    sudo chown -R mysql:mysql /mnt/data/mysql/log 
    sudo mysqld --initialize
    sudo service mysql start

然后就可以通过查看日志,知道重新初始化的用户密码了,但是进去之后会一直提示

ERROR 1820 (HY000): You must reset your password 
    using ALTER USER statement before executing this statement.

这个时候就用如下的方法解决

step 1: SET PASSWORD = PASSWORD('your new password');
    step 2: ALTER USER 'root'@'localhost' PASSWORD EXPIRE NEVER;
    step 3: flush privileges;

然后重启服务,就可以正常的运行了

如果日志丢了,找不到root的用户,可以修改 /etc/mysql/mysql.conf.d/mysqld.cnf 在mysqld下面添加一句

skip-grant-tables

然后重启服务,这样就可以直接使用root登录了

mysql
    update user set authentication_string=PASSWORD("你要设的密码") where User='root';     #修改密码
    update user set plugin="mysql_native_password"; #如果没这一行可能也会报一个错误，因此需要运行这一行
    flush privileges;

重启mysq,如果再次遇到之上的错误就按之前的方式来解决.  
到这里,单机的mysql已经配置解决完成. 下面将进入到mysql的集群配置  
准备两台机器 10.20.100.235 10.20.100.236 其中235作为主机,236作为从机  
然后在235的/etc/apparmor.d/usr.bin.mysqld 中进行如下的配置

server_id=1
    log-bin=/mnt/data/mysql/binlog/master-bin
    log-bin-index=/mnt/data/mysql/binlog/master-bin.index
    expire_logs_days        = 10
    max_binlog_size   = 100M
    binlog_do_db           = crawler
    
    #skip_slave_start=1
    #init-connect = 'SET NAMES utf8mb4'
    #character-set-server = utf8mb4
    #default_storage_engine = InnoDB
    #expire_logs_days = 7
    #max_connections = 3000
    #max_connect_errors = 6000
    #slow_query_log = 1
    #long_query_time = 1
    #slow_query_log_file = /mnt/data/mysql/log/mysql-slow.log
    #performance_schema = 0
    #lower_case_table_names = 1
    #innodb_flush_log_at_trx_commit = 2
    #interactive_timeout = 28800
    #wait_timeout = 28800
    #max_allowed_packet = 512M
    #connect_timeout = 60
    #net_read_timeout = 120
    #innodb_buffer_pool_size = 16G
    #group_concat_max_len = 102400
    
    [client]
    port = 3306
    socket = /tmp/mysql.sock
    default-character-set = utf8mb4
    
    [mysqldump]
    quick
    max_allowed_packet = 1024M
    
    [myisamchk]
    key_buffer_size = 8M
    sort_buffer_size = 8M
    read_buffer = 4M
    write_buffer = 4M

在这里还会遇到数据访问的权限问题,编辑/etc/apparmor.d/usr.sbin.mysqld

mkdir -p /mnt/data/mysql/bin/log/
     sudo vi /etc/apparmor.d/usr.sbin.mysqld 
     在# Allow data dir access 后面添加
      /mnt/data/mysql/bin/log/ rwk,
      /mnt/data/mysql/binlog/** rwk,

重启数据库,然后发现问题

07:04:33 UTC - mysqld got signal 11 ;
    This could be because you hit a bug. It is also possible that this binary
    or one of the libraries it was linked against is corrupt, improperly built,
    or misconfigured. This error can also be caused by malfunctioning hardware.
    Attempting to collect some information that could help diagnose the problem.
    As this is a crash and something is definitely wrong, the information
    collection process might fail.
    
    key_buffer_size=16777216
    read_buffer_size=131072
    max_used_connections=0
    max_threads=151
    thread_count=0
    connection_count=0
    It is possible that mysqld could use up to 
    key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 76387 K  bytes of memory
    Hope that's ok; if not, decrease some variables in the equation.
    
    Thread pointer: 0x0
    Attempting backtrace. You can use the following information to find out
    where mysqld died. If you see no messages after this, something went
    terribly wrong...
    stack_bottom = 0 thread_stack 0x30000
    /usr/sbin/mysqld(my_print_stacktrace+0x3b)[0xe93d5b]
    /usr/sbin/mysqld(handle_fatal_signal+0x489)[0x78ab29]
    /lib/x86_64-linux-gnu/libpthread.so.0(+0x11390)[0x7f0609a83390]
    /usr/sbin/mysqld(_Z6ER_THDPK3THDi+0x1)[0x7a9401]
    /usr/sbin/mysqld(_ZN13MYSQL_BIN_LOG17generate_new_nameEPcPKc+0x71)[0xe1bc41]
    /usr/sbin/mysqld(_ZN13MYSQL_BIN_LOG26init_and_set_log_file_nameEPKcS1_+0x2f)[0xe1bd5f]
    /usr/sbin/mysqld(_ZN13MYSQL_BIN_LOG11open_binlogEPKcS1_mbbbP28Format_description_log_event+0x56)[0xe24fc6]
    /usr/sbin/mysqld[0x783821]
    /usr/sbin/mysqld(_Z11mysqld_mainiPPc+0xa49)[0x784919]
    /lib/x86_64-linux-gnu/libc.so.6(__libc_start_main+0xf0)[0x7f0608e27830]
    /usr/sbin/mysqld(_start+0x29)[0x77adb9]

好吧,进入这一步,就完全进入死局了,没办法解决掉,后面再找机会,进行配置吧,现在先用单机.