How to Create a FUNCTION, log-binmysql
Create a function in the master database of the MySQL master-slave replication machine and report the following error:
Error Code: 1418. this function has none of DETERMINISTIC, no SQL, or reads SQL DATA in its declaration and binary logging is enabled (you * might * want to use the less safe log_bin_trust_function_creators variable)
It turns out that the binary log option log-bin is enabled on the two MySQL servers of master-slave replication, and slave will copy data from the master, and some operations, for example, the results of function may be different on the master and slave, so there are potential security risks. Therefore, function creation is blocked by default.
There are two ways to solve this problem
1. Set the log_bin_trust_function_creators parameter to ON, so that the MySQL Server with log-bin enabled can create functions at will. There are potential data security issues here, unless you know exactly that the created function acts exactly the same on the master and slave.
You can set this parameter dynamically or specify this parameter to start the database server or modify the configuration file and then restart the server. It should be noted that the dynamic setting method will expire after the server is restarted.
Mysql> show variables like 'Log _ bin_trust_function_creators ';
Mysql> set global log_bin_trust_function_creators = 1;
In addition, if you create a function on the master, to copy a function to slave by means of master-slave replication, you also need to set the value of the above variable to ON in the slave with log-bin enabled (the variable setting will not be copied from the master to the slave., this must be noted), otherwise the master-slave replication will report an error.
2. Specify the function type
1 DETERMINISTIC
2 no SQL does not have SQL statements, and of course data will not be modified
3 reads SQL DATA only READS DATA, and certainly does not modify the DATA
For example: create definer = 'username' @ '%' reads SQL data function 'fn _ getitemclock' (I _itemid bigint, I _clock int, I _pos int) RETURNS int (11 )...
This is equivalent to explicitly notifying the MySQL server that this function will not modify data. Therefore, you can securely create and copy the function to the slave with log-bin enabled.
Mysql two-way hot standby slave does not generate log-bin
Run show slave status on the slave machine to check the slave machine status.
Mysql log_bin always shows off
1. Restart the service after modifying the configuration;
2. You can specify the log file name to check whether logs exist. For example:
Log-bin = bin. log
