I'm getting the following error; Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file' I've tried setting this up on 3 different machines now and initially, all seemed well, but when I went back to it, I had the same error, so I experimented on 2 other machines, both giving me the same. Below are my cnf files; my.cnf # # This group is read both both by the client and the server # use it for options that affect everything # [client-server] # # include all files from the config directory # !includedir /etc/my.cnf.d !includedir /etc/mysql my_master.cnf # MASTER MySQL database server configuration file created by master-slave-setup # this is read by the standalone daemon and embedded servers [server] [mysqld1] # # * Basic Settings # user = mysql pid-file = /var/run/mysqld/mysqld_master.pid socket = /var/run/mysqld/mysqld_master.sock port = 3306 basedir = /usr datadir = /var/lib/mysql_master tmpdir = /tmp lc-messages-dir = /usr/share/mysql skip-external-locking bind-address = 127.0.0.1 # # * Fine Tuning # key_buffer_size = 16M max_allowed_packet = 16M thread_stack = 192K thread_cache_size = 8 myisam_recover_options = BACKUP query_cache_limit = 1M query_cache_size = 16M log_error = /var/log/mysql/error_master.log server-id = 1 log_bin = /var/log/mysql/mysql-bin.log expire_logs_days = 10 max_binlog_size = 100M binlog_do_db = hn innodb_flush_log_at_trx_commit = 1 sync_binlog = 1 binlog-format = ROW character-set-server = utf8mb4 collation-server = utf8mb4_general_ci my_slave.cnf # SLAVE MySQL database server configuration file created by master-slave-setup # this is read by the standalone daemon and embedded servers [server] [mysqld2] # # * Basic Settings # user = mysql pid-file = /var/run/mysqld/mysqld_slave.pid socket = /var/run/mysqld/mysqld_slave.sock port = 3307 basedir = /usr datadir = /var/lib/mysql_slave tmpdir = /tmp lc-messages-dir = /usr/share/mysql skip-external-locking bind-address = 127.0.0.1 # # * Fine Tuning # key_buffer_size = 16M max_allowed_packet = 16M thread_stack = 192K thread_cache_size = 8 myisam_recover_options = BACKUP query_cache_limit = 1M query_cache_size = 16M log_error = /var/log/mysql/error_slave.log server-id = 2 #log_bin = /var/log/mysql/mysql-bin.log expire_logs_days = 10 max_binlog_size = 100M binlog_do_db = hn #binlog_ignore_db = exclude_database_name relay-log = /var/log/mysql/relay-bin #relay-log-index = /var/log/mysql/relay-bin.index #master-info-file = /var/log/mysql/master.info #relay-log-info-file = /var/log/mysql/relay-log.info character-set-server = utf8mb4 collation-server = utf8mb4_general_ci my_multi.cnf # MYSQLD_MULTI MySQL database server configuration file created by master-slave-setup [mysqld_multi] mysqld = /usr/bin/mysqld_safe mysqladmin = /usr/bin/mysqladmin user = multi_admin password = multipass Now I have tried changing the bingo_do_db to individual databases, as per the above and also left it commented out, but wasn't sure I wanted to be replicating the mysql database. I'm using mysqld_multi to start and stop the instances and believe I have setup the users correctly. I've added the following to my slave instance; CHANGE MASTER TO MASTER_HOST='127.0.0.1', MASTER_USER='replication', MASTER_PASSWORD='replication', MASTER_LOG_FILE='mysql-bin.000009', MASTER_LOG_POS=1652; The show slave status\G is as follows; MariaDB [(none)]> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Master_Host: 127.0.0.1 Master_User: replication Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin-000009 Read_Master_Log_Pos: 1652 Relay_Log_File: relay-bin.000001 Relay_Log_Pos: 4 Relay_Master_Log_File: mysql-bin-000009 Slave_IO_Running: No Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 1652 Relay_Log_Space: 248 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: NULL Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 1236 Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file' Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1 Master_SSL_Crl: Master_SSL_Crlpath: Using_Gtid: No Gtid_IO_Pos: 1 row in set (0.00 sec) UPDATE:- the below is a copy of the logs which may help; root@dl-sl-1:/var/log/mysql# dir error_master.log mysql-bin.000005 mysql-bin.000011 mysql-bin.index error_slave.log mysql-bin.000006 mysql-bin.000012 relay-bin.000004 mysql-bin.000001 mysql-bin.000007 mysql-bin.000013 relay-bin.index mysql-bin.000002 mysql-bin.000008 mysql-bin.000014 replication.txt mysql-bin.000003 mysql-bin.000009 mysql-bin.000015 mysql-bin.000004 mysql-bin.000010 mysql-bin.000016 root@dl-sl-1:/var/log/mysql# cat mysql-bin.index /var/log/mysql/mysql-bin.000001 /var/log/mysql/mysql-bin.000002 /var/log/mysql/mysql-bin.000003 /var/log/mysql/mysql-bin.000004 /var/log/mysql/mysql-bin.000005 /var/log/mysql/mysql-bin.000006 /var/log/mysql/mysql-bin.000007 /var/log/mysql/mysql-bin.000008 /var/log/mysql/mysql-bin.000009 /var/log/mysql/mysql-bin.000010 /var/log/mysql/mysql-bin.000011 /var/log/mysql/mysql-bin.000012 /var/log/mysql/mysql-bin.000013 /var/log/mysql/mysql-bin.000014 /var/log/mysql/mysql-bin.000015 /var/log/mysql/mysql-bin.000016 I'm also getting the below when I make changes with CHANGE MASTER TO... Last_SQL_Error: Error 'Operation CREATE USER failed for 'replication'@'%'' on query. Default database: ''. Query: 'CREATE USER 'replication'@'%' IDENTIFIED BY 'replication'' which I hadn't noticed before. This is pretty much on a clean install. Any and all help would be greatly appreciated.

We are creating a web application that will be used by companies as their business application every day, each company will have many employees and a lot of data. Is it wise for each company to have its own database or is there a better solution for all companies to be in one database? Now we use one database and each company has its own id and the employee has a company id. But the database is too loaded with data and the search is slow as well as difficult privacy management. I would like quick data search in the database, easy implementation and not excessive maintenance costs.

**-- EDIT -- Complete setup in the answer below. -- EDIT --** When I follow an article (link below) on how to setup a extra MariaDB or Mysql on Linux, and it works. However, I don't like the name "mysql1" & "mysql2" for instance names. But when I change just that aspect of the code, the server wont install when I run the start command. I wrote both setups here below for easy comparison: The one that does work where 2 instances are being made, and the one that ultimately doesn't work. I would very much love to figure how to make the one that doesn't work, work. Your help is very much appreciated! **- 1) Datadir (setup that works):** sudo mkdir -p -v /var/lib/mysql{1..2} sudo chown mysql:mysql -v /var/lib/mysql{1..2} **- 1) Datadir (setup that ultimately doesn't work):** sudo mkdir -p -v /var/lib/mysqlTest sudo chown mysql:mysql -v /var/lib/mysqlTest **- 2) Socket directory (setup that works):** sudo mkdir -p -v /usr/local/mysql/mdb{1..2} sudo chown -v -R mysql:mysql /usr/local/mysql/mdb{1..2} **- 2) Socket directory(setup that ultimately doesn't work):** sudo mkdir -p -v /usr/local/mysql/mdbTest sudo chown -v -R mysql:mysql /usr/local/mysql/mdbTest **- 3) Log directory (setup that works):** sudo mkdir -p -v /var/log/mysql{1..2} sudo chown -v -R mysql:mysql /var/log/mysql{1..2} **- 3) Log directory(setup that ultimately doesn't work):** sudo mkdir -p -v /var/log/mysqlTest sudo chown -v -R mysql:mysql /var/log/mysqlTest **- 4) I create the following file** "mysqld_multi_cls.cnf" Located at: /etc/mysql/ **- 5) content (setup that works):** # Configuration Settings for mysqld_multi , as well as separate instances. # [mysqld_multi] mysqld = /usr/bin/mysqld_safe mysqladmin = /usr/bin/mysqladmin [mysqld1] socket = /tmp/mysql.sock1 port = 3307 pid-file = /usr/local/mysql/mdb1/ansible-host1.pid1 datadir = /var/lib/mysql1 log-error = /var/log/mysql1/error.log log_bin = /var/lib/mysql1/mdb1-mysql-bin.log server-id = 1 expire_logs_days = 72 sync_binlog = 1 innodb_flush_log_at_trx_commit = 1 innodb_flush_method = O_DIRECT binlog_format = mixed gtid_strict_mode = 1 [mysqld2] socket = /tmp/mysql.sock2 port = 3308 pid-file = /usr/local/mysql/mdb2/ansible-host1.pid2 datadir = /var/lib/mysql2 log-error = /var/log/mysql2/error.log log-bin = /var/lib/mysql2/mdb2-mysql-bin.log server-id = 2 expire_logs_days = 64 sync_binlog = 1 innodb_flush_log_at_trx_commit = 1 innodb_flush_method = O_DIRECT binlog_format = mixed gtid_strict_mode = 1 **- 5) content(setup that ultimately doesn't work):** # Configuration Settings for mysqld_multi , as well as separate instances. # [mysqld_multi] mysqld = /usr/bin/mysqld_safe mysqladmin = /usr/bin/mysqladmin [mysqlTest] socket = /tmp/mysql.sock3 port = 3309 pid-file = /usr/local/mysql/mdbTest/ansible-host1.pid3 datadir = /var/lib/mysqlTest log-error = /var/log/mysqlTest/error.log log_bin = /var/lib/mysqlTest/mdbTest-mysql-bin.log server-id = 3 expire_logs_days = 72 sync_binlog = 1 innodb_flush_log_at_trx_commit = 1 innodb_flush_method = O_DIRECT binlog_format = mixed gtid_strict_mode = 1 **-6) And now we start the server:** sudo mysqld_multi --defaults-file=/etc/mysql/mysqld_multi_cls.cnf --log=/etc/mysql/multi_ins.log start - If done in the 1st manor, the database is now installing and everyone is happy. - But if done in the 2nd manor where I only changed the name and made a single (extra) instance, nothing happens. What am I missing? Cheers and thank you very much! Here is the article on Linkedin by Michael Amadi: https://www.linkedin.com/pulse/running-multiple-instances-mariadb-single-server-different-amadi

On my Ubuntu server, there are two mysql instances running at the same time. - A default, normal mysql instance - A secondary mysql instance running with mysqld_multi. The command sudo service mysql status correctly states the mysql service is active. I want to check the secondary instance's status so I use this command : sudo mysqld_multi report ...and I get : MySQL server from group: mysqld1 is not running which seems pretty inconsistent since I can connect to this mysql instance from my DB client. **Why does mysqld_multi tell me the instance is not running ?**

I am Avinash. I am willing to install an additional MySQL instance on a UAT server for testing purposes. I am using MySQL Community Server 5.5.58 installed on RHEL7.2. I am going through the MySQL Docs https://dev.mysql.com/doc/refman/5.5/en/multiple-servers.html , which tells me that I would require certain unique operating parameters such port, socket, pid, datadir and log files related parameters. To achieve better performance, it also recommends to use different tmpdir. This is well understood. I can either use different binaries or the same binary for this purpose. If I am using same binary, I'll have to use mysqld_multi and use the same config file. If I use mysqld_multi, I'll have to put different sections for mysqld daemon such as mysqld1, mysqld2 and so on. All using unique values for above mentioned operating parameters. **Please help me out here. Am I correct about mysqld_multi?** **My second question is about bind-address variable.** https://dev.mysql.com/doc/refman/5.5/en/server-options.html#option_mysqld_bind-address Here, it explains that "The MySQL server listens on a single network socket for TCP/IP connections. This socket is bound to a single address, but it is possible for an address to map onto multiple network interfaces." **My question is that will I need an additional NIC and an additional IP address for the second MySQL instance?** **For the existing instance, I have disabled the bind-address option. Do I need to add the bind-addresses for both the instances.** Also, the second instance will be used only by me (for now). Thanking you.

I have several MariaDB servers, each with multiple instances running. I'm trying to figure out how would I bootstrap a Galera cluster but specify which instance I want to launch i.e: node1, 2, and 3 each has instances 1-4 running via mysqld_multi, each running on their own port. I want to use instance 4 on each server for the cluster, so I shut each one down. On my test cluster (which has a single instance of MariaDB running) bootstrapping is simple: start it on the first node with galera_new_cluster then bring up the other two servers. How would I do the same thing but specify GNR "4" to bootstrap the cluster?

Yesterday, I setup mysqld-multi in my.cnf. It works fantastic, but there is still a further Problem. The first DB on socket 1 running as Slave from a other server. The second one from socket 2 should run as Master for a further Slave. The replication for DB 1 running successful. **My question is it possible to setup the master-slave environment for every socket? Is it basically possible to setup more than one master-slave configuration in my.cnf?** I couldn't found helpful hints in google or I doesn't have the correct search words. I'm happy for any helpful hints. My system is Debian 6 with mysql 5 and php. [mysqld1] read-only # comment out only on the productive dbmaster port = 3306 datadir = /var/lib/mysql pid-file = /var/lib/mysql/mysqld.pid socket = /var/lib/mysql/mysql.sock user = mysql log-error=/var/log/mysql1.log innodb_data_home_dir = /var/lib/mysql innodb_log_group_home_dir = /var/lib/mysqlinnolog relay-log = /var/log/mysql/master-relay-bin relay-log-index = /var/log/mysql/master-relay-bin.index log-bin = /var/log/mysql/relaylog/master-bin [mysqld2] port = 3307 datadir = /var/lib/mysql-data2/mysqld2 pid-file = /var/lib/mysql-data2/mysqld2/mysql.pid socket = /var/lib/mysql-data2/mysqld2/mysql.sock user = mysql log-error=/var/log/mysqld2.log log-bin = /var/log/data2/mysql-bin Both instance have a specific port and a separate Datadir. Is it possible to give each section in the snippet a unique ID? Can I put the Slave ID in section [mysqld1] and the master ID in [mysqld2]?

i have 2 table one node and relation and Second store buy each node. my code work fine but when sum multiple buy for each node result multiplied by number of purchases . table nodes +-------------+---------------+-------------+ | ancestor_id | descendant_id | path_length | +-------------+---------------+-------------+ | 1 | 1 | 0 | | 1 | 2 | 1 | | 1 | 3 | 2 | | 1 | 4 | 1 | | 1 | 5 | 2 | | 1 | 6 | 3 | | 1 | 7 | 4 | | 2 | 2 | 0 | | 2 | 3 | 1 | | 2 | 5 | 1 | | 2 | 6 | 2 | | 2 | 7 | 3 | | 3 | 3 | 0 | | 4 | 4 | 0 | | 5 | 5 | 0 | | 5 | 6 | 1 | | 5 | 7 | 2 | | 6 | 6 | 0 | | 6 | 7 | 1 | | 7 | 7 | 0 | +-------------+---------------+-------------+ table buy +-------------+---------------+-------------+ | userid | amount | +-------------+---------------+-------------+ | 2 | 1500 | | 7 | 2000 | +-------------+---------------+-------------+ mysql code SELECT DISTINCT users.descendant_id , SUM(CASE WHEN ances.ancestor_id = buys_ances.userid THEN 1 ELSE 0 END) level_compress FROM webineh_prefix_nodes_paths as users join webineh_user_buys as buys on (users.descendant_id = buys.userid ) join webineh_prefix_nodes_paths as ances on (users.descendant_id = ances.descendant_id ) join webineh_user_buys as buys_ances on (buys_ances.userid = ances.ancestor_id ) WHERE users.ancestor_id = 1 and (SELECT SUM(g2.amount) as amount FROM webineh_user_buys g2 where g2.userid = ances.ancestor_id group by g2.userid ) >= 1000 and (SELECT SUM(g1.amount) as amount FROM webineh_user_buys g1 where g1.userid = users.descendant_id group by g1.userid ) >= 1000 group by buys.userid ,ances.ancestor_id result when in curent purchases data users.descendant_id | users.ancestor_id | level_compress 2 | | 1 6 | | 2 **i need to show ancestor_id compressed id**

Let's say we have a mysql database on localhost with table A and table B. Is it possible to fetch details from a 3rd party mysql server to which we have login info and which allows external access? Let's say there are 3 tables in the database we can connect to - table A2, B2 and C2. How do you fetch the rows from table C2 and make it VIRTUALY accessible (as table C) from within the original mysql server (just like A and B), just like if it was hosted on it?

I was made a setup to create multiple instances on the ubuntu machine. When I start mysql instances mysqld1 is running but mysqld2 is not running. root@ubuntu:/var/lib# mysqld_multi report Reporting MySQL servers MySQL server from group: mysqld1 is running MySQL server from group: mysqld2 is not running Below is my my.cnf file : [client] port = 3306 socket = /var/run/mysqld/mysqld.sock [mysqld_safe] socket = /var/run/mysqld/mysqld.sock nice = 0 [mysqld] basedir = /usr tmpdir = /tmp skip-external-locking bind-address = 127.0.0.1 key_buffer = 16M max_allowed_packet = 16M thread_stack = 192K thread_cache_size = 8 myisam-recover = BACKUP query_cache_limit = 1M query_cache_size = 16M expire_logs_days = 10 max_binlog_size = 100M [mysqld_multi] mysqld = /usr/bin/mysqld_safe mysqladmin = /usr/bin/mysqladmin log = /var/log/mysqld_multi.log user = multi_admin password = admin123 [mysqld1] port = 3306 datadir = /var/lib/mysql pid-file = /var/lib/mysql/mysqld.pid socket = /var/lib/mysql/mysql.sock user = mysql log-error = /var/log/mysql1.err [mysqld2] port = 3307 datadir = /var/lib/mysql-databases/mysqld2 pid-file = /var/lib/mysql-databases/mysqld2/mysql.pid socket = /var/lib/mysql-databases/mysqld2/mysql.sock user = mysql log-error = /var/log/mysql2.err [mysqldump] quick quote-names max_allowed_packet = 16M [mysql] [isamchk] key_buffer = 16M !includedir /etc/mysql/conf.d/ When I check my error log file mysql2.err, the error was like, 130120 18:41:59 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql-databases/mysqld2 130120 18:41:59 [Warning] Can't create test file /var/lib/mysql-databases/mysqld2/ubuntu.lower-test 130120 18:41:59 [Warning] Can't create test file /var/lib/mysql-databases/mysqld2/ubuntu.lower-test 130120 18:41:59 [Note] Plugin 'FEDERATED' is disabled. /usr/sbin/mysqld: Can't find file: './mysql/plugin.frm' (errno: 13) 130120 18:41:59 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it. 130120 18:41:59 InnoDB: Initializing buffer pool, size = 8.0M 130120 18:41:59 InnoDB: Completed initialization of buffer pool 130120 18:41:59 InnoDB: Operating system error number 13 in a file operation. InnoDB: The error means mysqld does not have the access rights to InnoDB: the directory. InnoDB: File name ./ibdata1 InnoDB: File operation call: 'create'. InnoDB: Cannot continue operation. 130120 18:41:59 mysqld_safe mysqld from pid file /var/lib/mysql-databases/mysqld2/mysql.pid ended Below is my apparmor file. #include /usr/sbin/mysqld { capability dac_override, capability sys_resource, capability setgid, capability setuid, network tcp, /etc/hosts.allow r, /etc/hosts.deny r, /etc/mysql/*.pem r, /etc/mysql/conf.d/ r, /etc/mysql/conf.d/* r, /etc/mysql/*.cnf r, /usr/lib/mysql/plugin/ r, /usr/lib/mysql/plugin/*.so* mr, /usr/sbin/mysqld mr, /usr/share/mysql/** r, /var/log/mysql.log rw, /var/log/mysql.err rw, /var/lib/mysql/ r, /var/lib/mysql/** rwk, /var/log/mysql/ r, /var/log/mysql/* rw, /{,var/}run/mysqld/mysqld.pid w, /{,var/}run/mysqld/mysqld.sock w, /sys/devices/system/cpu/ r, } My second instance data directory path is : /var/lib/mysql-databases/mysqld2/mysql Please let me know how to fix this issue. Thanks in advance.