Tilde character is not the culprit, exclamation mark is.
Double-quoted !~ triggers history substitution in your local shell. Note the inner single-quotes don't matter locally (compare this, similar situation; the inner quotes will matter in the remote shell though).
You need to make !~ appear single-quoted for the local shell. The problem is you also want single-quotes in the remote shell. For this reason you cannot easily nest quotes.
You can disable history substitution in your local shell with set +H and stick to double-quoting locally. But note you don't want $4 or $2 to be expanded by any shell, including the local one. Therefore you need to single-quote locally anyway; or to escape $ with \, e.g. \$2.
Escaping with \ can help with unwanted history substitution only with unquoted \!. Double-quoted \! will prevent history substitution, but this \ won't disappear, it will break things later.
Possible approaches:
Unquoted and escaped !:
# history substitution may be enabled
ssh ip "awk '{ if ( \$4 ~ /ro,/ && \$2 "\!"~ /sys/ ) {print \$2} }' /proc/mounts"
^^^^-double-quoted locally-^^^^ ^^^^^^^-double-quoted locally-^^^^^^^
unquoted locally ^^
Disabled history substitution:
set +H
ssh ip "awk '{ if ( \$4 ~ /ro,/ && \$2 !~ /sys/ ) {print \$2} }' /proc/mounts"
# re-enable history expansion (invoke `set -H') here if needed
Single-quoting everything but the inner single-quotes that will be crucial in the remote shell:
# history substitution may be enabled
ssh ip 'awk '"'"'{ if ( $4 ~ /ro,/ && $2 !~ /sys/ ) {print $2} }'"'"' /proc/mounts'
^^^^ ^^^^^^^^^^^^-single-quoted locally-^^^^^^^^^^^^ ^^^^^^^^^^^^^
^ double-quoted locally ^
Single-quoting everything locally, double-quoting in the remote shell. This will push your problems to the remote shell, so you will need to deal with them anyway:
ssh ip 'awk "{ if ( \$4 ~ /ro,/ && \$2 !~ /sys/ ) {print \$2} }" /proc/mounts'
Surprise! We escaped $ but not !~; and there is no set +H, yet the command works.
This is because ssh spawns a non-interactive shell on the remote side. Non-interactive Bash starts with history expansion disabled by default (note the remote shell may or may not be Bash in the first place, it may or may not support history at all). This makes the main problem disappear. The same mechanism would help you if you run your original ssh command non-interactively, e.g. in a local script (although you would still need to escape $ characters).