New answers tagged

0

Create the server and client cert/key files on the master server and then copy only the client cert/key files to each slave server and configure SSL replication. So basically you have the same client cert and key on all slave servers. If a slave gets compromised change the replication user's credentials.


1

dont use "mysqldump" while restoring backup. Use "mysql". mysqldump is for taking backup, not for restoring.


1

Backing up the database is a best practice right before upgrading to any version. You can use mysqldump, Xtrabackup, mydumper . Replacing MySQL community packages with Percona Server packages should do the job just make sure to execute mysql_upgrade after starting up PS 5.6 the first time. Percona Server is a free, fully compatible, enhanced, open ...


2

Do not use GUIDs -- they have a serious performance problem at scale. (This is because of their randomness.) Within 5 years (probably within 1 year), you will decide on some significant change and this 'last id' question will be moot. You are "removing" regularly? Why? If you are doing a REPLACE or DELETE, you probably have some other unique key on ...


0

Ok, I solved it. I could not solve the actual problem of using the user variable nested in the subquery, as I think there is simply no way to do this (please comment, or answer if there is), but this is how I solved it, in case anyone is curious: SELECT @idx:=@idx+1 as idx, date, weekday, id, added_on , from_table FROM ( SELECT show_after, ...


0

Plan A: ALTER IGNORE TABLE users ADD UNIQUE(email, alt_email, mobile and alt_mobile); This will go through the table, copy it over, ignoring any dups. I am, however, hesitant to say that it will properly handle NULLs in those columns. Plan B: CREATE TABLE new LIKE users; ALTER TABLE new ADD UNIQUE(email, alt_email, mobile and alt_mobile); INSERT ...


0

Short answer: I recovered from backup During my work day, I do perform several mysqldumps of my work database and mysql. But I could not perform an import since it would fail with the same Error 1130. I stopped the db, I moved my data/mysql directory out of the way, and then dropped in my March 1st data/mysql. I restarted the database and found I could ...


1

The hard limit would be dependent on the datatype you use for the auto increment field, but once hit that threshold you'd get an overflow error of some type. If that really is a concern, then I'd suggest using something that wouldn't be as limiting such as a GUID (uuid). http://mysqlserverteam.com/storing-uuid-values-in-mysql-tables/ http://www....


0

Grab a snapshot of the data. Turn on the "general log" to get a complete listing of the queries. After a while, turn off the general log. (It fills up the disk rapidly.) The tricky part is to do steps 1 and 2 'simultaneously'. If you have LVM set up on the server, you can come close to "instantaneous". The reason for getting the data snapshot exactly ...


2

Consider enforcing this combination of columns as a unique constraint at the database level. That won't be possible until the existing duplicates are cleaned out. But it will help prevent this problem from creeping back into the data. You should define the 4 columns as a non-unique index now since this process will involving lots of comparisons using them. ...


-4

put "", like this mysqldump -u root -p databasename > "c:/home/databasename_bkup.sql"


1

You cannot use @variable_name to declare variable. @ can be used only for session variables. Change your code to this, it will work DELIMITER // CREATE PROCEDURE one_off_update_user_info_view_type() BEGIN DECLARE batchsize BIGINT DEFAULT 1; END //


2

You use DECLARE in compound-statement syntax to declare a local variable, cursor, condition or handler. However, your variable @batchsize is a session global variable due to the presence of the @ prefix, and it is an error to attempt to redeclare a global variable. Change the name of your variable from @batchsize to batchsize, and then read up on MySQL ...


1

For the alternative way to count these: select day(date) as day, hour(date) as hour, count(1) as c, sum(a=1) as a1, sum(a=0) as a0, (sum(a=1) / count(1)) as pct from t group by hour, date(date) I added the date to the group by so the same hour of different days do not get counted together - change that the way you need.



Top 50 recent answers are included