mysql 增加資料檔案

mariadb 增加資料測試

參數檔案

    innodb_data_file_path =/data/3306/data/ibdata1:10M:autoextend

改為

    innodb_data_file_path =/data/3306/data/ibdata1:10M:autoextend;/data/3306/data/ibdata2:10M:autoextend

啟動就報  

    160811 20:08:33 [ERROR] InnoDB: syntax error in innodb_data_file_path or size specified is less than 1 megabyte

後面的檔案不管怎麼加都不行，需要将第一個檔案的autoextend屬性去掉

innodb_data_file_path = /data/3306/data/ibdata1:10M;/data/3306/data/ibdata2:10M:autoextend

啟動不再報 syntax error,卻報：

InnoDB: Error: data file //data/3306/data/ibdata1 is of a different size

InnoDB: 1152 pages (rounded down to MB)

InnoDB: than specified in the .cnf file 640 pages!

160811 20:25:11 InnoDB: Could not open or create data files.

160811 20:25:11 InnoDB: If you tried to add new data files, and it failed here,

160811 20:25:11 InnoDB: you should now edit innodb_data_file_path in my.cnf back

160811 20:25:11 InnoDB: to what it was, and remove the new ibdata files InnoDB created

160811 20:25:11 InnoDB: in this failed attempt. InnoDB only wrote those files full of

160811 20:25:11 InnoDB: zeros, but did not yet use them in any way. But be careful: do not

160811 20:25:11 InnoDB: remove old data files which contain your precious data!

原來是第一個檔案的大小不再是10M,需要改成它實際的大小 

1152*16K/1024＝18M

于是改為

innodb_data_file_path = /data/3306/data/ibdata1:18M;/data/3306/data/ibdata2:10M:autoextend

這次啟動正常了

注：參數中設定了  innodb_data_home_dir = 

如果不設定，它會預設 innodb_data_home_dir 為 datadir，啟動時就會報錯：

160811 20:34:09  InnoDB: Operating system error number 2 in a file operation.

InnoDB: The error means the system cannot find the path specified.

InnoDB: If you are installing InnoDB, remember that you must create

InnoDB: directories yourself, InnoDB does not create them.

InnoDB: File name .//data/3306/data/ibdata1

InnoDB: File operation call: 'create'.

InnoDB: Cannot continue operation.

相關參數：

innodb_data_home_dir	This is a directory shared set InnoDB tables. If you do not set in my.cnf,InnoDB will use the MySQL  datadir  directory the default directory. If you set an empty string, you can set the absolute path in innodb_data_file_path .
innodb_data_file_path	Separate data file specified path and size. The full path of data files and by the innodb_data_home_dir combination of settings here. Specified in the file size in MB. Therefore, the size of the file must be specified after the "M". InnoDB also supports the abbreviation "G", 1G = 1024M. Starting from 3.23.44, large file support in those operating systems can set the data size greater than 4 GB. In some data files on the operating system must be less than 2 GB. The sum of the data file size to reach at least 10 MB. In MySQL-3.23 in this parameter must be explicitly specified in my.cnf. In MySQL-4.0.2 and later you do not need this, the system will default directory in the MySQL  datadir  create a 16 MB self-expansion (auto-extending) the data file ibdata1. You can also use a native partition (RAW raw disk partitions (raw devices)) as a data file, how to specify them in detail in my.cnf see Section 12.1.
innodb_mirrored_log_groups	To protect the data set copy of the log file group number, default is 1. In the my.cnf in digital format.
innodb_log_group_home_dir	InnoDB log file path. Must be set the same value with innodb_log_arch_dir . If you do not explicitly specify the default directory in the MySQL  datadir  create two 5 MB file size ib_logfile ....
innodb_log_files_in_group	Log log file group number. InnoDB in ring mode (circular fashion) to a file. Value 3 is recommended. In the my.cnf in digital format.
innodb_log_file_size	Log group size of each log file (in MB). If n is log log file group number, then the ideal values for the 1M to the following set of buffer pool (buffer pool) size 1 / n. Larger values can reduce the number of times to flush the buffer pool to reduce disk I / O. However, a large log file means that the collapse takes longer to recover data. Log files must be less than 2 GB, 3.23.55 and 4.0.9 above is less than 4 GB. In the my.cnf in digital format.
innodb_log_buffer_size	InnoDB log files written to the log disk before the buffer size. Ideal value of 1M to 8M. Large log buffer allows a transaction to run without the log saved to disk but only to the transaction is committed (commit). Therefore, if there is a large transaction, set a large log buffer can reduce disk I / O. In the my.cnf in digital format.
innodb_flush_log_at_trx_commit	Usually set to 1, meaning that the transaction log has been submitted written to disk before the transaction can run longer, and service after the collapse of the repair capacity. If you are willing to weaken the security, or you are running small transactions, it can be set to 0, in order to reduce the disk write log file I / O. This option defaults to 0.
innodb_log_arch_dir	The directory where fully written log files would be archived if we used log archiving. This set of parameters must  innodb_log_group_home_dir same. Starting from 4.0.6, you can ignore this parameter.
innodb_log_archive	This value is usually set to 0. Since the recovery from a backup (recovery) for MySQL using its own log files, which are usually no longer need to archive InnoDB log files. This option defaults to 0.
innodb_buffer_pool_size	InnoDB is used to cache data and index memory buffer size. Greater access to the data set can reduce disk I / O. In a dedicated database server you can set it to 80% of physical memory. Do not set it too, because the use of physical memory the operating system may affect competition in the page call. In the my.cnf in digital format.
innodb_additional_mem_pool_size	InnoDB is used to store data dictionary (data dictionary) information and other internal data structures (internal data structures) of memory combinations (memory pool) size. Ideal value of 2M, if there are more tables you need here to re-allocation. If InnoDB exhausted the pool of all the memory it allocates memory from the operating system and error messages written to the MySQL error log. In the my.cnf in digital format.
innodb_file_io_threads	InnoDB file I / O threads. Usually set to 4, but under Windows you can set a higher value to improve disk I / O. In the my.cnf in digital format.
innodb_lock_wait_timeout	Rollback (rooled back) before, InnoDB transaction will wait out the time (in seconds). InnoDB will automatically check its own lock table and rollback in the transaction when the deadlock. If you use  LOCK TABLES command, or in a transaction with the use of other transaction-safe table handler (transaction safe table handlers than InnoDB), it may occur an InnoDB deadlock can not be noticed. In this case, the timeout will be used to solve this problem. The default value is 50 seconds. In the my.cnfin digital format.
innodb_flush_method	This parameter is only associated with Unix. This parameter defaults to fdatasync . The other set items to  O_DSYNC . This affects only the log file dump under Unix  fsync  dump data. InnoDB version from 3.23.40b started on Unix to use  fsync  designated  fdatasync  way, designated for the use of O_SYNC O_DSYNC  way. Since this is also in some Unix environments, so some of the problems in 'data' versions are not being used.
innodb_force_recovery	Warning: This parameter can only be damaged you want from a database dump (dump) data for emergency use! May set the value of the range of 1 - 6. See the following section 'Forcing recovery' in order to understand the specific meaning of this parameter. Parameter value greater than 0 represents the InnoDB prevents users from modifying data security. Starting from 3.23.44, this parameter is available. In themy.cnf in digital format.
innodb_fast_shutdown	Lack InnoDB insert buffer empty before closing. This operation may take several minutes, in extreme cases can take several hours. If this parameter is set according to 1, InnoDB will skip this process and direct closure. Starting from 3.23.44 and 4.0.1, this parameter is available. Starting from 3.23.50, this parameter defaults to 1.
innodb_thread_concurrency	InnoDB InnoDB service will attempt to use the operating system process is less than or equal to the value set here. This parameter defaults to 8. If the computer system performance shows that many low or innodb_monitor  paternity signal thread, etc., this value should be smaller. If your computer system has a processor and disk I system, this value can be set higher to take full advantage of your system resources. Proposed to be located is the number of processors + number of disks. Starting from 3.23.44 and 4.0.1, this parameter is available. In the my.cnf in digital format.