Tag Info

New answers tagged

1

Yes you can use innodb_file_per_table = 1 for your slaves. Additionally take below points in your consideration If you have same version on MySQL on both Master and Slave. You can directly take backup and setup replication from Master to Slave by adding innodb_file_per_table = 1 on slave in my.cnf Please have a look at How to set Master Slave ...


0

Look for Amazon instructions. With RDS, the instructions are roughly: Upgrade the Slave first. Replication works when the Master is 'older'. Failover, but do not turn on the Slave. Upgrade the Slave (was Master) Turn on replication (START SLAVE) Wait for replication to catch up before using the Slave.


2

I assume the * is only for demonstration purposes. You can use GROUP_CONCAT to aggregate strings from several rows into one string. In the example below I only include carname, you probably want some more info: SELECT c.carname , GROUP_CONCAT(p.partname) FROM carparts as cp JOIN car as c ON cp.carid = c.id JOIN parts as p ON p.partid = ...


2

you structure looks good . except that you could merge the amount to the part table . as every part is strictly associates with price. also, only reason you will have duplicate rows is if any of your dimensions tables do not follow strict primary constraints. Car - id (pk) - carname - image - category - status Parts - partid (pk) - partname - ...


1

Not knowing what the full relationships are or sample data, try this FK correction first to see if it fixes the problem: SELECT * FROM carparts INNER JOIN car on carparts.carid = car.id INNER JOIN parts on parts.partid = carparts.carpartid INNER JOIN amount on amount.amountid = carparts.amountid WHERE status = 1


0

You should use MySQL's BENCHMARK() function Here are my posts on how to use it Apr 03, 2012 : How to use the 'select benchmark' command on a procedure Apr 16, 2013 : MySQL: CAST + SUBSTR vs. FLOOR + MOD BTW if you do configuartion changes, you should restart mysqld immediately after the configuration changes are in place. Then, run your I/O ...


1

In MySQL 5.6, mysqldump can do that for you with --dump-slave=1 and --single-transaction. It is the same as --master-data=1, but here is how the log file and position are retrieved: --master-data=1: log file and position from SHOW MASTER STATUS; --dump-slave=1: Relay_Master_Log_File,Exec_Master_Log_Pos from SHOW SLAVE STATUS\G. --dump-slave=1 will run do ...


1

If you want to setup slave from another slave steps would be as follows Flush tables with read lock to lock the slave so it should not get updates show slave status\G and note master co-ordinates Take backup now using mysqldump unlock tables to unlock the slave transfer that backup and restore it to new one Hope you have issues grant replication slave on ...


0

I have found that if tables get too large the server load can go up dramatically and CPU usage skyrocket.


0

The SHUTDOWN privilege can only be used when running mysqladmin Apr 22, 2013 : /usr/libexec/mysqld: Normal shutdown, but my team doesn't do that? Mar 06, 2013 : How to properly kill MySQL? Feb 28, 2013 : mysql restart won't kill child processes on CentOS Taking away SHUTDOWN grant will stop users from running mysqladmin shutdown. Your Actual ...


0

For cases like this, there is "one extra connection available". Anyone with SUPER priv (eg. "root") can get in to kill the DROP, which would have uncorked the mess. However, a prerequisite for doing that is to not have users and applications log in as "root". Instead they should have their own logins, preferable (for security reasons) limited to the ...


-1

Try "mysqladmin flush-hosts" on the mysql server.


0

There are multiple resolutions however in most cases you will not be able to do them unless you have access and the ability to: Downgrade PHP Upgrade the MYSQL Libraries to match the version of MYSQL on the server. Change the my.cnf file Comment out OLD_PASSWORDS = 1 restart mysql Create or modify a user with the password desired Uncomment out ...


1

We have few replications in my shop and anytime we stop a master, prior to doing so we stop replication on the slave with: STOP SLAVE;


1

Could you give us some info on the replication config in your config files. For sure if you replicate one db at a time and you need to add one, you won't have a choice to change your config then to restart your instances. If you can I'll suggest more a full replication. less trouble and if you need to add dbs, there is no problem there.


1

I have seen your problem before Problem #1 You have /etc/mysql/conf.d/mycustom.cnf and the log-bin line is not being read Problem #2 Chances are /etc/mysql/conf.d/my.cnf exists and has the following line [mysqld] log-bin=OFF That's why the binlogs start with a filename of OFF Some feel log-bin=OFF disables binlogging. The MySQL Documentation on ...


0

Corrupted mysql database prevents reading the user permissions during the dump process and hence you are out of it. It seems that you are able to restore the user tables. So, better to stay away from possible mix of data files written by old version and new version of mysql server. First, make sure to restore and make it run as usual. Then, take a full ...


1

Let's walk through the steps. This will make some of the suggestions so far not significant. Scan 2.6 million rows out of 4-5M. Please verify by finding out how many rows have store_id = 1. Scan 2.6 million rows out of 4-5M again. (Yes, a single pass with an OR would be better since there is no way to make use of INDEX(store_id, data_index), which is ...


0

USE db_99; do something with db_1 or 2 or 3; will not replicate because of your binlog-do-db and replicate-do-db. (The later is redundant in your case.) This is because replication (except for "wild" versions) is based on the USE, not on any explicit db spelled out in the statement. Is it a "bug"? Well, a lot of people get burned by it.


0

You need the answers based on the storage engine in use Does inserting a row count as a disk write? For MyISAM, I would say yes because a MyISAM table keeps its total row count in its header. Inserting a row into a MyISAM would consequently impose an additional write to the table header to update the row count. For InnoDB, the disk writing activity to ...


0

A "write" is distinct from a "disk write". Writes include writes to tmp tables. This pattern avoids having to do subtracts (for Handler%, not for Innodb%): FLUSH STATUS; perform some query; SHOW SESSION STATUS LIKE 'Handler%'; Innodb_% values are only GLOBAL; Handler% are SESSION, then added to the GLOBAL tallies. Handler% is done at an engine-agnostic ...


2

The query has a lot of bloat: Two group by, the second is completely unnecessary. Three levels of nesting, the last is also completely unnecessary. It uses GROUP_CONCAT() to find all the product_id that have rows with 'southern' and rows with 'proper'. Not the best way in my opinion. The 1189 AS query_id seems to return redundant information (the same ...



Top 50 recent answers are included