mysql 姜_Mysql使用姜的my.cnf

1、把原先安装好的data目录删除掉，或者删除其中内容。再将姜的配置文件上传，修改下innoodb_log_file_size的大小，更改为1G。要不然太大，虚拟机没那么大空间。

上传好后，进行初始化的时候报错，没有生成相应配置文件中的内容，比如undo，redo文件。而且iostat -xm 3来看磁盘并没有写入，说明文件应该没有生成。

1 [root@localhost mysql]# scripts/mysql_install_db --user=mysql2 Installing MySQL system tables...2020-02-16 22:55:27 0 [Note] ./bin/mysqld (mysqld 5.6.27-log) starting as process 9078...3 [root@localhost mysql]# cd data4 [root@localhost data]# ls5 bin.index error.log ibdata1 mysql test6 [root@localhost data]# ll7 总用量 12292

8 -rw-rw----. 1 mysql mysql 0 2月 16 22:55bin.index9 -rw-rw----. 1 mysql mysql 1890 2月 16 22:55error.log10 -rw-rw----. 1 mysql mysql 12582912 2月 16 22:55ibdata111 drwx------. 2 mysql mysql 6 2月 16 22:55mysql

2、查看错误日志的输出内容

1 [root@localhost data]# less error.log2 2020-02-16 22:55:27 9078 [Note] InnoDB: Using atomics to refcount buffer pool pages3 2020-02-16 22:55:27 9078 [Note] InnoDB: The InnoDB memory heap isdisabled4 2020-02-16 22:55:27 9078[Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins5 2020-02-16 22:55:27 9078 [Note] InnoDB: Memory barrier isnot used6 2020-02-16 22:55:27 9078 [Note] InnoDB: Compressed tables use zlib 1.2.3

7 2020-02-16 22:55:27 9078[Note] InnoDB: Using Linux native AIO8 2020-02-16 22:55:27 9078[Note] InnoDB: Using CPU crc32 instructions9 2020-02-16 22:55:27 9078 [Note] InnoDB: Initializing buffer pool, size = 128.0M

10 2020-02-16 22:55:27 9078[Note] InnoDB: Completed initialization of buffer pool11 2020-02-16 22:55:27 9078 [Note] InnoDB: The first specified data file ./ibdata1 did not exist: a new database to be created!

12 2020-02-16 22:55:27 9078 [Note] InnoDB: Setting file ./ibdata1 size to 12MB13 2020-02-16 22:55:27 9078[Note] InnoDB: Database physically writes the file full: wait...14 2020-02-16 22:55:27 7f1748a2d740 InnoDB: Operating system error number 2 ina file operation.15 InnoDB: The error means the system cannot find the path specified.16 InnoDB: If you are installing InnoDB, remember that you must create17 InnoDB: directories yourself, InnoDB does not create them.18 2020-02-16 22:55:27 9078 [ERROR] InnoDB: File /redolog/ib_logfile101: 'create' returned OS error 71.19 2020-02-16 22:55:27 9078 [ERROR] InnoDB: Cannot create /redolog/ib_logfile10120 2020-02-16 22:55:27 9078 [ERROR] Plugin 'InnoDB'init function returned error.21 2020-02-16 22:55:27 9078 [ERROR] Plugin 'InnoDB' registration asa STORAGE ENGINE failed.22 2020-02-16 22:55:27 9078 [ERROR] Unknown/unsupported storage engine: InnoDB23 2020-02-16 22:55:27 9078[ERROR] Aborting24

25 2020-02-16 22:55:27 9078[Note] Binlog end26 2020-02-16 22:55:27 9078[Note] unregister_replicator OK27 2020-02-16 22:55:27 9078 [Note] ./bin/mysqld: Shutdown complete

不太清楚是什么错误，把配置文件的路径信息先注释掉试试。

3、再次修改配置文件内容

1 [root@localhost data]# vi /etc/my.cnf2 [client]3 user=root4 password=111111

5

6 [mysqld]7 ########basic settings########8 server-id = 11

9 port = 3306

10 user =mysql11 #bind_address = 10.166.224.32

12 #autocommit = 0

13 character_set_server=utf8mb414 skip_name_resolve = 1

15 max_connections = 800

16 max_connect_errors = 1000

17 #datadir = /data/mysql_data18 datadir = /usr/local/mysql/data19 transaction_isolation = READ-COMMITTED20 explicit_defaults_for_timestamp = 1

21 join_buffer_size = 134217728

22 tmp_table_size = 67108864

23 tmpdir = /tmp24 max_allowed_packet = 16777216

25 #sql_mode = "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER"

26 interactive_timeout = 1800

27 wait_timeout = 1800

28 read_buffer_size = 16777216

29 read_rnd_buffer_size = 33554432

30 sort_buffer_size = 33554432

31 ########log settings########32 log_error =error.log33 #slow_query_log = 1

34 #slow_query_log_file =slow.log35 #log_queries_not_using_indexes = 1

36 #log_slow_admin_statements = 1

37 #log_slow_slave_statements = 1

38 #log_throttle_queries_not_using_indexes = 10

39 #expire_logs_days = 90

40 #long_query_time = 2

41 #min_examined_row_limit = 100

42 ########replication settings########43 #master_info_repository =TABLE44 #relay_log_info_repository =TABLE45 log_bin =bin.log46 #sync_binlog = 1

47 #gtid_mode =on48 #enforce_gtid_consistency = 1

49 #log_slave_updates50 #binlog_format =row51 #relay_log =relay.log52 #relay_log_recovery = 1

53 #binlog_gtid_simple_recovery = 1

54 #slave_skip_errors =ddl_exist_errors55 ########innodb settings########56 #innodb_page_size = 8192

57 #innodb_buffer_pool_size =6G58 #innodb_buffer_pool_instances = 8

59 #innodb_buffer_pool_load_at_startup = 1

60 #innodb_buffer_pool_dump_at_shutdown = 1

61 #innodb_lru_scan_depth = 2000

62 #innodb_lock_wait_timeout = 5

63 #innodb_io_capacity = 4000

64 #innodb_io_capacity_max = 8000

65 #innodb_flush_method =O_DIRECT66 #innodb_file_format =Barracuda67 #innodb_file_format_max =Barracuda68 #innodb_log_group_home_dir = /redolog/

69 #innodb_undo_directory = /undolog/

70 innodb_undo_logs = 128

71 innodb_undo_tablespaces = 3

72 #innodb_flush_neighbors = 1

73 innodb_log_file_size =1G74 innodb_log_buffer_size = 16777216

75 #innodb_purge_threads = 4

76 #innodb_large_prefix = 1

77 #innodb_thread_concurrency = 64

78 #innodb_print_all_deadlocks = 1

79 #innodb_strict_mode = 1

80 innodb_sort_buffer_size = 67108864

81 ########semi sync replication settings########82 plugin_dir=/usr/local/mysql/lib/plugin83 plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"

84 #loose_rpl_semi_sync_master_enabled = 1

85 #loose_rpl_semi_sync_slave_enabled = 1

86 #loose_rpl_semi_sync_master_timeout = 5000

87

88 [mysqld-5.7]89 innodb_buffer_pool_dump_pct = 40

90 "/etc/my.cnf" [dos] 96L, 2926C written

注释掉68、69条后，再删除data后，初始化操作。

这里边注释的两条与datadir其实有个很重要的问题：就是datadir、redo、undo路径下相关的文件都是在Mysql运行的时候写入的，所以这三个路径下的文件必须是mysql mysql权限。(所以在未注释掉68\69之前，初始化会报错)

1 [root@localhost mysql]# scripts/mysql_install_db --user=mysql2 Installing MySQL system tables...2020-02-16 23:00:14 0 [Note] ./bin/mysqld (mysqld 5.6.27-log) starting as process 9162...3 OK4

5 Filling help tables...2020-02-16 23:01:16 0 [Note] ./bin/mysqld (mysqld 5.6.27-log) starting as process 9201...6 OK7

8 To start mysqld at boot time you have to copy9 support-files/mysql.server to the right place foryour system10

11 PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !

12 To doso, start the server, then issue the following commands:13

14 ./bin/mysqladmin -u root password 'new-password'

15 ./bin/mysqladmin -u root -h localhost.localdomain password 'new-password'

16

17 Alternatively you can run:18

19 ./bin/mysql_secure_installation20

21 which will also give you the option of removing the test22 databases and anonymous user created by default. This is

23 strongly recommended forproduction servers.24

25 See the manual formore instructions.26

27 You can start the MySQL daemon with:28

29 cd . ; ./bin/mysqld_safe &

30

31 You can test the MySQL daemon with mysql-test-run.pl32

33 cd mysql-test ; perl mysql-test-run.pl34

35 Please report any problems at http://bugs.mysql.com/

36

37 The latest information about MySQL isavailable on the web at38

39 http://www.mysql.com

40

41 Support MySQL by buying support/licenses at http://shop.mysql.com

42

43 WARNING: Found existing config file ./my.cnf on the system.44 Because this file might be inuse, it was not replaced,45 but was used in bootstrap (unless you used --defaults-file)46 and when you later start the server.47 The new default config file was created as ./my-new.cnf,48 please compare it with your file and take the changes you need.49

50 WARNING: Default config file /etc/my.cnf exists on the system51 This file will be read by defaultby the MySQL server52 If you do not want to use this, either remove it, or use the53 --defaults-file argument to mysqld_safe when starting the server

43行内容就是个小错误，但是没有报error，只报了一个warning。

这个错误应该是检测到还有其它的my.cnf，按理说不应该，因为/etc/下的各版本的my.cnf都已经改了后缀名，应该不会出这个错误。不懂。

5、再次查看新生成的error.log中输出 的内容

1 [root@localhost mysql]# cat data/error.log2 2020-02-16 23:00:14 9162 [Note] InnoDB: Using atomics to refcount buffer pool pages3 2020-02-16 23:00:14 9162 [Note] InnoDB: The InnoDB memory heap isdisabled4 2020-02-16 23:00:14 9162[Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins5 2020-02-16 23:00:14 9162 [Note] InnoDB: Memory barrier isnot used6 2020-02-16 23:00:14 9162 [Note] InnoDB: Compressed tables use zlib 1.2.3

7 2020-02-16 23:00:14 9162[Note] InnoDB: Using Linux native AIO8 2020-02-16 23:00:14 9162[Note] InnoDB: Using CPU crc32 instructions9 2020-02-16 23:00:14 9162 [Note] InnoDB: Initializing buffer pool, size = 128.0M

10 2020-02-16 23:00:14 9162[Note] InnoDB: Completed initialization of buffer pool11 2020-02-16 23:00:14 9162 [Note] InnoDB: The first specified data file ./ibdata1 did not exist: a new database to be created!

12 2020-02-16 23:00:14 9162 [Note] InnoDB: Setting file ./ibdata1 size to 12MB13 2020-02-16 23:00:14 9162[Note] InnoDB: Database physically writes the file full: wait...14 2020-02-16 23:00:14 9162 [Note] InnoDB: Setting log file ./ib_logfile101 size to 1024MB15 InnoDB: Progress in MB: 100 200 300 400 500 600 700 800 900 1000

16 2020-02-16 23:00:41 9162 [Note] InnoDB: Setting log file ./ib_logfile1 size to 1024MB17 InnoDB: Progress in MB: 100 200 300 400 500 600 700 800 900 1000

18 2020-02-16 23:01:13 9162 [Note] InnoDB: Data file ./undo001 did not exist: newto be created19 2020-02-16 23:01:13 9162 [Note] InnoDB: Setting file ./undo001 size to 10MB20 2020-02-16 23:01:13 9162[Note] InnoDB: Database physically writes the file full: wait...21 2020-02-16 23:01:13 9162 [Note] InnoDB: Data file ./undo002 did not exist: newto be created22 2020-02-16 23:01:13 9162 [Note] InnoDB: Setting file ./undo002 size to 10MB23 2020-02-16 23:01:13 9162[Note] InnoDB: Database physically writes the file full: wait...24 2020-02-16 23:01:13 9162 [Note] InnoDB: Data file ./undo003 did not exist: newto be created25 2020-02-16 23:01:13 9162 [Note] InnoDB: Setting file ./undo003 size to 10MB26 2020-02-16 23:01:13 9162[Note] InnoDB: Database physically writes the file full: wait...27 2020-02-16 23:01:13 9162 [Note] InnoDB: Opened 3undo tablespaces28 2020-02-16 23:01:13 9162 [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile029 2020-02-16 23:01:13 9162 [Warning] InnoDB: New log files created, LSN=48424

30 2020-02-16 23:01:13 9162 [Note] InnoDB: Doublewrite buffer not found: creating new

31 2020-02-16 23:01:13 9162[Note] InnoDB: Doublewrite buffer created32 2020-02-16 23:01:13 9162 [Note] InnoDB: 128rollback segment(s) are active.33 2020-02-16 23:01:13 9162[Warning] InnoDB: Creating foreign key constraint system tables.34 2020-02-16 23:01:13 9162[Note] InnoDB: Foreign key constraint system tables created35 2020-02-16 23:01:13 9162[Note] InnoDB: Creating tablespace and datafile system tables.36 2020-02-16 23:01:13 9162[Note] InnoDB: Tablespace and datafile system tables created.37 2020-02-16 23:01:13 9162 [Note] InnoDB: Waiting forpurge to start38 2020-02-16 23:01:13 9162 [Note] InnoDB: 5.6.27 started; log sequence number 0

39 2020-02-16 23:01:14 9162[Note] Binlog end40 2020-02-16 23:01:14 9162[Note] unregister_replicator OK41 2020-02-16 23:01:14 9162[Note] InnoDB: FTS optimize thread exiting.42 2020-02-16 23:01:14 9162[Note] InnoDB: Starting shutdown...43 2020-02-16 23:01:15 9162 [Note] InnoDB: Shutdown completed; log sequence number 1508991

44 2020-02-16 23:01:16 9201 [Note] InnoDB: Using atomics to refcount buffer pool pages45 2020-02-16 23:01:16 9201 [Note] InnoDB: The InnoDB memory heap isdisabled46 2020-02-16 23:01:16 9201[Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins47 2020-02-16 23:01:16 9201 [Note] InnoDB: Memory barrier isnot used48 2020-02-16 23:01:16 9201 [Note] InnoDB: Compressed tables use zlib 1.2.3

49 2020-02-16 23:01:16 9201[Note] InnoDB: Using Linux native AIO50 2020-02-16 23:01:16 9201[Note] InnoDB: Using CPU crc32 instructions51 2020-02-16 23:01:16 9201 [Note] InnoDB: Initializing buffer pool, size = 128.0M

52 2020-02-16 23:01:16 9201[Note] InnoDB: Completed initialization of buffer pool53 2020-02-16 23:01:16 9201 [Note] InnoDB: Opened 3undo tablespaces54 2020-02-16 23:01:16 9201 [Note] InnoDB: Highest supported file format isBarracuda.55 2020-02-16 23:01:16 9201 [Note] InnoDB: 128rollback segment(s) are active.56 2020-02-16 23:01:16 9201 [Note] InnoDB: Waiting forpurge to start57 2020-02-16 23:01:16 9201 [Note] InnoDB: 5.6.27 started; log sequence number 1508991

58 2020-02-16 23:01:16 9201[Note] Binlog end59 2020-02-16 23:01:16 9201[Note] unregister_replicator OK60 2020-02-16 23:01:16 9201[Note] InnoDB: FTS optimize thread exiting.61 2020-02-16 23:01:16 9201[Note] InnoDB: Starting shutdown...62 2020-02-16 23:01:17 9201 [Note] InnoDB: Shutdown completed; log sequence number 1509001

现在可以看到没有报错信息了!

6、此时Mysql命令还不能用，因为mysql 的bin目录还没有配置到环境变更 中，具体看以下内容：

1 [root@localhost mysql]# mysql

2 -bash: mysql: 未找到命令

3 [root@localhost mysql]# mysqld

4 -bash: mysqld: 未找到命令　　　　　　　　----可以看到此时还不能使用mysql 命令

5 [root@localhost mysql]# export PATH=/usr/local/mysql/bin:$PATH　　　　----这是临时修改的环境变更 ，可以生效一次，永久解决这个问题，需要如下边编辑/etc/profile6 [root@localhost mysql]# echo $PATH7 /usr/local/mysql/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin8 [root@localhost mysql]# vi /etc/profile9 profile profile.d/

10 [root@localhost mysql]# vi /etc/profile11 profile profile.d/

12 [root@localhost mysql]# vi /etc/profile13 # /etc/profile14

15 # System wide environment and startup programs, forlogin setup16 # Functions and aliases go in /etc/bashrc17

18 # It's NOT a good idea to change this file unless you know what you

19 # are doing. It's much better to create a custom.sh shell script in

20 # /etc/profile.d/ to make custom changes to your environment, as this

21 # will prevent the need for merging infuture updates.22

23 pathmunge () {24 case ":${PATH}:" in

25 *:"$1":*)26 ;;27 *)28 if [ "$2" = "after"] ; then29 PATH=$PATH:$1

30 else

31 PATH=$1:$PATH32 fi33 esac34 }35

36

37 if [ -x /usr/bin/id ]; then38 if [ -z "$EUID"]; then39 # ksh workaround40 EUID=`/usr/bin/id -u`41 UID=`/usr/bin/id -ru`42 fi43 USER="`/usr/bin/id -un`"

44 LOGNAME=$USER45 MAIL="/var/spool/mail/$USER"

46 fi47

48 # Path manipulation49 if [ "$EUID" = "0"]; then50 pathmunge /usr/sbin51 pathmunge /usr/local/sbin52 else

53 pathmunge /usr/local/sbin after54 pathmunge /usr/sbin after55 fi56

57 HOSTNAME=`/usr/bin/hostname 2>/dev/null`58 HISTSIZE=1000

59 if [ "$HISTCONTROL" = "ignorespace"] ; then60 export HISTCONTROL=ignoreboth61 else

62 export HISTCONTROL=ignoredups63 fi64

65 export PATH USER LOGNAME MAIL HOSTNAME HISTSIZE HISTCONTROL66

67 # By default, we want umask to get set. This sets it forlogin shell68 # Current threshold for system reserved uid/gids is 200

69 # You could check uidgid reservation validity in

70 # /usr/share/doc/setup-*/uidgid file71 if [ $UID -gt 199 ] && [ "`/usr/bin/id -gn`" = "`/usr/bin/id -un`"]; then72 umask 002

73 else

74 umask 022

75 fi76

77 for i in /etc/profile.d/*.sh /etc/profile.d/sh.local ; do78 if [ -r "$i" ]; then79 if [ "${-#*i}" != "$-" ]; then80 . "$i"81 else82 . "$i" >/dev/null83 fi84 fi85 done86

87 unset i88 unset -f pathmunge89export PATH=/usr/local/mysql/bin:$PATH　　---姜说：路径要在$PATH的前面，不然会报错90 "/etc/profile" 77L, 1858C written91 [root@localhost mysql]# ./etc/profile92 -bash: ./etc/profile: 没有那个文件或目录93 [root@localhost mysql]# cd /94 [root@localhost /]# ./etc/profile95 -bash: ./etc/profile: 权限不够96 [root@localhost /]# source /etc/profile97 [root@localhost /]# mysql -V98 mysql Ver 14.14 Distrib 5.6.27, for linux-glibc2.5 (x86_64) using EditLine wrapper99 [root@localhost /]# mysql100 ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (111)　　--有这一行说明mysql命令已经可以使用了101 [root@localhost /]#

现在启动和登录进，应该还会报sock文件错误。先不管了，和视频走，升5.7