mysql一些操作
mysql配置
1 [client] 2 port = 3306 3 socket =/var/mysql_data/run/mysql.sock 4 default-character-set = utf8mb4 5 6 [mysql] 7 #prompt="MySQL [\d]> " 8 #no-auto-rehash 9 10 [mysqld] 11 port = 3306 12 socket = /var/mysql_data/run/mysql.sock 13 14 basedir = /usr/local/mysql 15 datadir = /var/mysql_data/data 16 pid-file =/var/mysql_data/run/mysql.pid 17 user = u_mysql 18 bind-address = 0.0.0.0 19 server-id = 3 20 skip_name_resolve = ON 21 22 init-connect = 'SET NAMES utf8mb4' 23 character-set-server = utf8mb4 24 25 skip-name-resolve 26 #skip-networking 27 back_log = 300 28 29 max_connections = 1000 30 max_connect_errors = 6000 31 open_files_limit = 65535 32 table_open_cache = 128 33 max_allowed_packet = 500M 34 binlog_cache_size = 1M 35 max_heap_table_size = 8M 36 tmp_table_size = 16M 37 38 read_buffer_size = 2M 39 read_rnd_buffer_size = 8M 40 sort_buffer_size = 8M 41 join_buffer_size = 8M 42 key_buffer_size = 4M 43 44 thread_cache_size = 8 45 46 query_cache_type = 1 47 query_cache_size = 8M 48 query_cache_limit = 2M 49 50 ft_min_word_len = 4 51 52 log_bin = mysql-bin 53 binlog_format = row 54 expire_logs_days = 7 55 #控制binlog日志文件保留时间，超过保留时间的binlog日志会被自动删除。 56 57 log_error = /var/mysql_data/log/mysql-error.log 58 slow_query_log = 1 #是否启用慢查询日志，1为启用，0为禁用 59 long_query_time = 1 #SQL语句运行时间阈值，执行时间大于参数值的语句才会被记录下来 60 slow_query_log_file = /var/mysql_data/log/mysql-slow.log #指定慢查询日志文件的路径和名字，可使用绝对路径指定； 61 62 performance_schema = 0 63 explicit_defaults_for_timestamp 64 65 #lower_case_table_names = 1 66 67 skip-external-locking 68 69 default_storage_engine = InnoDB 70 #default-storage-engine = MyISAM 71 innodb_file_per_table = 1 72 innodb_open_files = 500 73 innodb_buffer_pool_size = 64M 74 innodb_write_io_threads = 4 75 innodb_read_io_threads = 4 76 innodb_thread_concurrency = 0 77 innodb_purge_threads = 1 78 innodb_flush_log_at_trx_commit = 2 79 innodb_log_buffer_size = 2M 80 innodb_log_file_size = 32M 81 innodb_log_files_in_group = 3 82 innodb_max_dirty_pages_pct = 90 83 innodb_lock_wait_timeout = 120 84 85 bulk_insert_buffer_size = 8M 86 myisam_sort_buffer_size = 8M 87 myisam_max_sort_file_size = 10G 88 myisam_repair_threads = 1 89 90 interactive_timeout = 28800 91 wait_timeout = 28800 92 93 [mysqldump] 94 quick 95 max_allowed_packet = 500M 96 97 [myisamchk] 98 key_buffer_size = 8M 99 sort_buffer_size = 8M 100 read_buffer = 4M 101 write_buffer = 4M
添加指定权限的操作用户
CREATE USER 'user1'@'%' IDENTIFIED BY '07fa533360d9';
#'%' - 所有情况都能访问
#‘localhost’ - 本机才能访问
#’111.222.33.44‘ - 指定 ip 才能访问 grant all privileges on 想授权的数据库.* to 'user1'@'%';
#授权用户
FLUSH PRIVILEGES;
刷新权限
